Showing posts with label powerbi. Show all posts
Showing posts with label powerbi. Show all posts

Reading Notes #510


Good Monday, Already time to share new reading notes. Here is a list of all the articles, blog posts, and podcast episodes that catch my interest during the week. 

If you think you may have interesting content, share it!

The Suggestion of the week

  • Incremental ASP.NET to ASP.NET Core Migration (Taylor Southwick) - Migrating an application is not an easy task. This post shares the new GitHub repos that the .NET team puts in place with guidance, pattern, and toolings to help us. Really interesting, bookmark that one for future references.

Cloud

Programming

Data

Miscellaneous

~frank

Reading Notes #505

Me, thinking I could work with the dogs

Good Monday, Already time to share new reading notes. Here is a list of all the articles, blog posts, and podcast episodes that catch my interest during the week.

If you think you may have interesting content, share it!

Cloud

Programming

Miscellaneous

~frank


Reading Notes #483


Already Tuesday! Time for a new Reading Notes post; a list of all the articles, blog posts, and books that catch my interest during the week and that I found interesting. It's a mix of the actuality and what I consumed.

This week is the Cloud Summit (https://azuresummit.live) is an 11-day free conference that focuses on Azure, there is surely a session that will catch your interest.

You think you may have interesting content, share it!


Cloud


Programming


Miscellaneous


~Frank


Reading Notes #469

Every Monday, I share my "reading notes". Those are a curated list of all the articles, blog posts, podcast episodes, and books that catch my interest during the week and that I found interesting. It's a mix of the actuality and what I consumed. 


You think you may have interesting content, share it!

Cloud

Programming

Books

cover of Think Again: The Power of Knowing What You Don't Know

Think Again: The Power of Knowing What You Don't Know
 

Author: Adam M. Grant
- I really like this book. It validates that it's okay to rethink decisions, to change your mind. The goal is to have (and keep) an open mindset. It seems easier than it is, of course. I heard amazing comments about this author, so it's probably not the last one I will read from him.





Miscellaneous


~ Frank


Reading Notes #333

flag-28555_640Cloud


Programming


Data


Reading Notes #330

IMG_20180602_073928

Cloud


Programming


Data

  • Apply a Filter to a Slicer (Mike Carlo) - Sooooo useful. If you don't know how to do it (yet), just watch the video, you'll thanks me later.

Miscellaneous



Reading Notes #328

Cloud

  • 10 Reasons to Use Durable Functions (Mark Heath) - To celebrate his new course about durable function, Mark shares with us his top10 of the best reason with should use durable functions.

Programming


Data

  • Power BI Desktop May Feature Summary (Amanda Cofsky) - The monthly updates is always a great new. This month shows more about that new Q&A feature... You may not know about it, but you really want to use it...
  • Data Encodings and Layout (Clemens Vasters) - Very useful and deep article that provides the best practices for data encoding for different type of situation.

Books

Exactly What to Say, The Magic Words for Influence and Impact
(Phil M. Jones)
I listen to this audio book and really enjoy it. Simple powerful key works selection tat helps us to get where we want to go. It was only about two hours long and I listen to it in one shoot. And I’m mostly certain I will listen to it again.
ISBN 9780692881958



Miscellaneous



Reading Notes #325

can-chat-chatting-362Suggestion of the week



Cloud



Programming



Data



Miscellaneous


Books




Reading Notes #320

IMG_20180307_195017

Cloud


Programming


Data



Book

    Making Time () - I really enjoyed reading that book. I've been watching Bob's YouTube Channel for a while. ANd was happy to ear about his new book. In Making Time, a little bit like in his videos, Bob explains how he build stuff... but this time instead of a furniture or a room, it's a new career/ business. A short book (88 pages) but a lot of fun.  


Reading Notes #315

36145725596_6536a4f8aa_cSuggestion of the week



Cloud


Programming


Databases


Miscellaneous





Photo credit: nickpettican on Visual Hunt / CC BY-NC-ND

Reading Notes #311

DateTimeImg2Suggestion of the week



Cloud


Programming

  • Styling Social Media Icon Lists in CSS (Mark Heath) - Yeah right, we can read CSS and probably hack some stuff... But it's excellent to learn how to do simple things the good way. And this post shows exactly that.


Data


Miscellaneous


Reading Notes #309

419HCloud


Programming


Databases


Miscellaneous



Reading Notes #303

logo-glyphSuggestion of the week

  • Writing tests in Postman (joyce) - With all the connected things and all the API in our system, this post shows a brilliant and simple way to test all those external calls.

Cloud


Programming


Data


Miscellaneous




Reading Notes #302

Autumn

Cloud


Programming


Data


Miscellaneous




Reading Notes #301

300love

Programming


Data

Miscellaneous


  • My First Year as an MVP, part 1 (Jen Kuntz) - Interesting post. It feels soooooo familiar, and yet so far now. I look forward to meeting you in March fellow Canadian MVP.


Reading Notes #300

300loveCloud


Programming


Data


Miscellaneous



Lessons Learned with Power Bi and Dynamic DAX Expressions

Since its availability, I try to use Power Bi as often as I can. It is so convenient, to build visual to explain data coming from a ton of possible data source. And it's a breeze to share it with clients, or colleagues. However, this post is not an info-commercial about Power Bi, it's about sharing some challenges I got trying to prepare a report and how I fix it.

The Data Source


The context is simple, all transactions are in one table, and I have a second table with a little information related to clients. To that, I personally like to add a calendar table, because it simplifies my life.

Datamodel

For this report, it is very important to but a slicer by Client.

The Goal


I needed to have one report that shows for every customer three different Year To Date (YTD) total. The classic YTO, a YTD but when the beginning of the years is, in fact, when the client started is enrolment, and the last one was a rolling twelve.
It looks pretty simple, and in fact, it's not that complicated. Let's examine each total formula one by one.

Classic Year To Date Total


Before we get started, it's a good practice to reuse Mesure to simplify our formula, and to explicit expression. Let's create a Measure for the Total Sales, that will be used inside our other formulas.

TotalSales = SUM('Sales'[Total])

Now the Year To Date, is simple to add by adding a New Measure and entering the formula:

YDTClassic = TOTALYDT([TotalSales], 'Calendar'[Date])

If you activate the Preview feature of Power Bi, it could be even easier. Look for the button New Quick Measure and select the Total Year To Date, fill up the form and voila!

QuickMeasure

The generated formula looks a bit different because Power Bi managed the error in h expression.

TotalYTD = 
IF(
    ISFILTERED('Calendar'[Date]),
    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy."),
    TOTALYDT([TotalSales], 'Calendar'[Date].[Date])
)

Anniversary Year To Date Total


I spent more time then I was expecting on that one. Because in the Online DAX documentation it is said that the formula TOTALYDT accept a third parameter to specify the end of the year. So if I had only one client, with a fix anniversary date (or a fiscal year) this formula will work assuming, the special date is April 30th.
TOTALYDT([TotalSales], 'Calendar'[Date], "04-30")
However, in my case, the ending date changes at with every client. I'm sure right now you are thinking that's easy Frank just set a variable and that it! Well, it won't work. The thing is the formula is expecting a static literal, no variable aloud even if it returns a string.
The workaround looks at first a bit hard, but it's not that complex. We need to write our own YTD formula. Let's look at the code, and I will explain it after.

Anniversary YTD = 
VAR enddingDate =   LASTDATE(Company[EnrolmentDate])
VAR enddingMonth =  MONTH ( enddingDate )
VAR enddingDay =    DAY ( enddingDate )
VAR currentDate =   MAX ( Calendar[Date] )
VAR currentYear =   YEAR ( currentDate )
VAR enddingThisYear =   DATE ( currentYear, enddingMonth, enddingDay )
VAR enddingLastYear =   DATE ( currentYear - 1, enddingMonth, enddingDay )
VAR enddingSelected =   IF ( enddingThisYear < currentDate, enddingThisYear, enddingLastYear )
RETURN
    CALCULATE (
        [TotalSales] ,    
        DATESBETWEEN(Calendar[Date],enddingSelected,currentDate)    
    )
First lines are all variable's declaration. They are not required, but I found it easier to understand when things are very explicit. Since I'm slicing my report by companies putting the LASTDATE is just a way not avoid errors. It should have only one record. Then we extract year, month, and day.
The last variable enddingSelected identify if the anniversary (the end date) is pasted or not in the curent calendar year.
The calculate function is returning the TotalSales between the last anniversary date and today.

Rolling twelve Total


For the last formula, the rolling twelve we will re-use the previous code, but in a simpler way since the end date is always yesterday.

Rolling 12 Total = 
VAR todayDate = TODAY()
VAR todayMonth =    MONTH ( todayDate )
VAR todayDay =  DAY ( todayDate ) 
VAR todayYear = YEAR ( todayDate ) 
VAR enddingLastYear =   DATE ( todayYear - 1, todayMonth, TodayDayVar +1) 
RETURN
    CALCULATE (
        [TotalSales] ,
        DATESBETWEEN( Calendar[Date], enddingLastYear, todayDate)   
    )

Wrap it up


I definitely learned a few things with that Power Bi session, but it turns out to be pretty easy. Again, leave a comment or send me an email if you have any comments or questions I will be very happy to ear from you.


References


Reading Notes #291

Blue-container

Cloud


Programming


Miscellaneous




Reading Notes #289

IMG_20170710_092837

Cloud


Programming


Databases

  • Migrating to Azure SQL Database (Gavin Payne) - Very interesting and complete post that regroups references and gives details about some of the alternatives when it's migration time.


Reading Notes #284

IMG_20170609_092421Cloud

Programming

Miscellaneous