Interaction: Measure, Grouping and Relationships (Year-to-Date, Prior Year/period etc)

[Anonymous]

Hi,

I have a general question regarding how measure works in geregards to relations and groupings. There must be something i'm not understanding regarding the way Power BI works.

I'm currently trying to built a financial statement with different layers for Revenue, costs etc etc and I wanted to show YTD, QTD and Prior Period values per grouping. The measures need to also be correct when I apply a "Month"  or "Quarter" filter. When I just put the dates, and show these measures then it would be fine, but when I drag in the additional groupings/categories then it would show incorrect values.  To try and understand it, I'm using the below sample file to see if understand and show my problem with fictional "sales" data.

As you can see in the below table that normal Sales per Color is correctly shown, but the YTD values differ with each Color. I thought the below relationships would be used to also show the correct values for YTD. Do i need to add in the filters in the below measure? But then my question would be... whats the point of the relationship if i have to manually filter it in the measur itself? Would then not need to create like multiple measures for each category? (And if there a sub categories for each sub category?

Is there a tutorial or something that explains me why it does not work? I think im not understanding something fundementally with how it works. I would like to understand so that i can troubleshoot problems myself in the future.

Example file:

https://easyupload.io/26a8od 

Normal Table:

Simple Matrix view

YTD Measure

Sales YTD = 
    TOTALYTD(
        SUM(Sales[Sales]),
        'Calendar'[Date],
        Sales[Sales] < TODAY()
    )

Relationships

With kind regards,

Ryan

[Anonymous]

Thanks for the help! It works this way for this specific data set.

But it does not work for some reason for my original data set. The YTD value does not even work currently even without the Categories. When Selecting Days, it works, but it now does not show the correct value for month level. And when apply the "Color" Categories it wont work aswell.

I managed to replicate the problem with fictional data, see link below:

https://easyupload.io/9qzd5n 

Total Cost and YTD seems to work for "Day" level, but wont show on Month level?

Total Cost YTD = 
    TOTALYTD(
        SUM(Costs[Amount in local currency]),
        'Calendar'[Date]
    )

1 to Many Relation with DateTable:

I tried to check what the difference is between the two files in regards of formatting etc, but could not find it.

[grantsamborn]

Hi

Add a calculated column to your Calendar table:

PeriodDt = STARTOFMONTH('Calendar'[Date])

and format it as 'mmmm yyyy'

Then use that instead of 'Month' in your matrix on the right.

Hope this helps.

[rodrigosan · Accepted solution]

Create your date table with the code below and try, please

****************************************************************************************************************

let fdCalendar = (StartDate as date, EndDate as date) as table =>

let
Duration = Duration.Days(Duration.From(EndDate -StartDate)),
Source = List.Dates(StartDate, Duration, #duration(1, 0, 0, 0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
#"Inserted Day" = Table.AddColumn(#"Changed Type", "Day", each Date.Day([Date]), Int64.Type),
#"Inserted Day Name" = Table.AddColumn(#"Inserted Day", "Day Name", each Date.DayOfWeekName([Date]), type text),
#"Short Day Name" = Table.AddColumn(#"Inserted Day Name", "Short Day Name", each Text.Start([Day Name], 3), type text),
#"Inserted Month" = Table.AddColumn(#"Short Day Name", "Month", each Date.Month([Date]), Int64.Type),
#"Entered Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.MonthName([Date]), type text),
#"Abbreviated Month Name" = Table.AddColumn(#"Entered Month Name", "Abbreviated Month Name", each Text.Start([Month Name], 3), type text),
#"Entered Year" = Table.AddColumn(#"Abbreviated Month Name", "Year", each Date.Year([Date]), Int64.Type),
#"Entered Quarter" = Table.AddColumn(#"Entered Year", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
Semester = Table.AddColumn(#"Entered Quarter", "Semester", each if [Month] <= 6 then 1 else 2, Int64.Type),
#"Entered Week of Month" = Table.AddColumn(Semester, "Week of Month", each Date.WeekOfMonth([Date]), Int64.Type),
#"Inserted Day of Year" = Table.AddColumn(#"Entered Week of Month", "Inserted Day of Year", each Date.DayOfYear([Date]), Int64.Type),
#"Days Inserted in Month" = Table.AddColumn(#"Inserted Day of Year", "Days of Month", each Date.DaysInMonth([Date]), Int64.Type),
YearMes = Table.AddColumn(#"Days Inserted in Month", "YearMonth", each Text.Combine({Text.From([Year]), Text.PadStart(Text.From([Month]), 2, "0")})),
#"Month/Year" = Table.AddColumn(YearMes, "Month Year", each Text.Combine({[Abbreviated Month Name], "/", Text.Middle(Text.From([Year]), 2)})),
#"Changed Type1" = Table.TransformColumnTypes(#"Month/Year",{{"YearMonth", Int64.Type}, {"Month Year", type text}})
in
#"Changed Type1"

in
fdCalendar
****************************************************************************************************************

Did I solve your problem?
Please mark as solution so others can find this solution.
https://www.linkedin.com/in/rodrigosanpbi/

[Anonymous]

Hi all,

Thanks guys, the new calendar did fix the problem! Can you explain to me why my Calendar was not sufficient as a date table? Then the answer would be also beneficial to others that might have this problem.

I thought i only needed:

• Day
• Week
• Month
• Year

Was it the formatting or different columns that a required to have an adequate dateTable?

I'm a bit confused on this.

Thank you

[rodrigosan]

By my analysis your table was converted to a date table.
That was the only difference I found.
But I don't usually convert the date table to this format.
I always work with her standard.

Did I solve your problem?
Please mark as solution so others can find this solution.
https://www.linkedin.com/in/rodrigosanpbi/

[Anonymous]

Hi

Thanks for the quick reply. I thought I had to convert to a date table in order to use Time Intelligence. 

I also converted your table to a date table. And it still works for some reason. I'm not really sure what is wrong then.

[rodrigosan]

Could you explain how you created your date table?
Your date field setting cannot contain times together, only the date.

Did I solve your problem?
Please mark as solution so others can find this solution.
https://www.linkedin.com/in/rodrigosanpbi/

[Anonymous]

I simply listed all the days manually from 1/1/2021 tot 31/12/2023 in Excel and then applied =TEXT( , "mmm"), =Month() and =YEAR() formulas to get the specific month name and year etc.

Then I created a table and loaded into power BI, and marked the days as date table.

[rodrigosan]

I see that is correct.
I noticed that in the last file you shared your data table has only a period of one year.
So in that case there would be no dates for comparison with the previous year.

Did I solve your problem?
Please mark as solution so others can find this solution.
https://www.linkedin.com/in/rodrigosanpbi/

[Anonymous]

Yeah, i had no data for last year, as i wanted to only getting it right for current year for now. Thats why I wanted to do the YTD calculations. Would it be a problem if the data has 1 year, and the datetable has multiple years? 

[rodrigosan]

No, just a question if your base has more data

[Anonymous]

Currently it only has data from 2022 up untill the end of december 2022. (Budgets)

[grantsamborn]

Hi

I didn't see your reply when I posted but I AGREE 100% that a well-formed, comprehensive date table (preferably in Power Query) is a MUST.

[rodrigosan]

Hello.
See if the solution in the link works for you. :https://5pwtcn-my.sharepoint.com/:f:/g/personal/rodrigosan_5pwtcn_onmicrosoft_com/EtmK9dM7vUdFshoJV0...

Did I solve your problem?
Please mark as solution so others can find this solution.
https://www.linkedin.com/in/rodrigosanpbi/

[grantsamborn]

1. Change the relationship between Sales and Calendar to many-to-1.

2. Delete the last line of [Sales YTD].

Seems to work for me.