Chapter 15 YTD Functions Exercise 63 & 64 Topic is solved

This is a dedicated forum for people that have questions about the book Supercharge Power BI. In the event of errors in the book, the error information will be in this forum.
Posts: 2
Joined: Fri Aug 02, 2019 11:01 am

Chapter 15 YTD Functions Exercise 63 & 64

Postby Deehambone » Thu Aug 08, 2019 5:28 am

There is something that I am not understanding about the data bars for these columns. I've written the columns exactly as the solution says and included the DAX in the attached image for reference. It makes sense to me that the FYTD 30 June column "starts" (has the lowest sales value) in the July row. Similarly, it makes sense to me that the FYTD 31 March column "starts" in the April row. It is because I have specified the end of year date as the month in the row before before those rows.

What I don't get is why the largest data bar for each column is still in the December row? I would think that the largest data bar would be in the row with the date specified in the filter (June for FYTD 30 June, March for FYTD 31 March) as that is the new year end date so the YTD would be largest in those months. There must be something I am not understanding about the function TotalTYD() when the optional filter is applied...

Would anyone care to explain? Does it have something to do with the order of the months in the matrix starting in the January month instead of starting at the starting month specified in the FYTD DAX formulas?
Snip.png (153.05 KiB) Viewed 820 times

Posts: 48
Joined: Thu Oct 19, 2017 12:57 pm

Re: Chapter 15 YTD Functions Exercise 63 & 64  Topic is solved

Postby RamanaV » Thu Aug 08, 2019 11:35 am

Your DAX formulas and your understanding is correct.
You used Calendar Year for Rows. So YTD started with January and ended with December. Smallest value in Jan and largest value in Dec
Your FYTD also started and ended correctly. July to June in first case and April to March in the second case.
In first case - smallest value in July and largest value in June (Please look at June value above July value)
In the second case - smallest value in April and largest value in March (again look at March value above April value)

To have the clarity, use a slicer and add only the months in the Financial year to Rows.
For e.g. have only July to June. Then you will have largest value for June in first case and for March in second case

Hope this helps

Ramana Varanasi
Training and Office Manager
Excelerator BI Pty. Ltd.

Posts: 2
Joined: Fri Aug 02, 2019 11:01 am

Re: Chapter 15 YTD Functions Exercise 63 & 64

Postby Deehambone » Thu Aug 15, 2019 6:03 am

RamanaV, thank you for your reply. I now see what was confusing me.

The image I posted before was filtering to show only data from 2003. However, the FYTD equations were operating across all the data (2001 to 2004) and only displaying what the matrix was filtered to show (2003). I see now that the reason the January rows were confusing me was because I couldn't "see" the rows from the previous year, 2002. For some reason I thought the January 2003 row would continue the YTD calculation from the December 2003 row at the bottom of the previous image. But that is incorrect. I see now by expanding the matrix to show data from 2001 through 2003 that the January 2003 row is actually continuing the calculation from December 2002, which makes sense and is what the DAX equation was supposed to do.

I've posted the new matrix showing data from 2001 through 2003. As RamanaV indicated in the reply, this made it much more clear to me that the FYTD equations were working as expected. Thanks again RamanaV!
FYTD 2001 to 2003.PNG
FYTD 2001 to 2003.PNG (126.28 KiB) Viewed 728 times

Return to “Help with the Book "Supercharge Power BI"”

Who is online

Users browsing this forum: No registered users and 1 guest