Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dear Community,
Problem:
YTD line is not drawn on a current month (today is 13th Aug 2019 so on August) when chart is viewed at month level of date hierarchy.
Data Model:
Calendar table as a dimension table
Values table as a fact table
Description:
I have values in Values table that I've wanted to sum year to date. The values are tagged with a date on a different field as usual. This date field is connected to the Calendar table as Calendar table 1-to-* Values table.
For the YTD result I have created a measure so that future values are ruled out to make the chart line look clean (I don't want the line to continue be drawn on future months and after a long search in the community only the following code was working). YTD code is
VAR CumulativeYTD = TOTALYTD(SUM(ValuesTable[Values]); 'Calendar'[Date] ) Return IF(MAX('Calendar'[Date])<=TODAY();CumulativeYTD)
The problem is that I have values in my Values table booked on August but these values are not shown in my chart since the chart doesn't show any data after July 2019. The reason seems to be that August is not over yet so the chart doesn't show any line on August yet (see attachment 1.).
To demonstrate, when I create a table in Power BI (attachment 2.) the values on August are seen and they exist. If I try to remove the date level from the date hierarchy in the table, August figures disappear right away (attachment 3.).
So, I need the chart line to show also YTD figures also on a current month when the chart is viewed on month level. Now it shows full YTD data only if it is viewed on date level which is impractical.
Please see also attachments 4. and 5. of the calendar table and the date hierarchy.
Thanks!
Solved! Go to Solution.
Hi @Dillinger
I create calendar table as below
calendar = ADDCOLUMNS ( CALENDARAUTO (), "mm/yy", FORMAT ( [Date], "mm/yy" ), "yy/mm", FORMAT ( [Date], "yy/mm" ), "year", YEAR ( [Date] ), "month", MONTH ( [Date] ) )
Create a measure
Measure = VAR CumulativeYTD = TOTALYTD ( SUM ( 'Table'[value] ), 'calendar'[Date], FILTER ( ALL ( 'calendar' ), 'calendar'[Date] <= TODAY () ) ) RETURN IF ( MAX ( 'calendar'[year] ) = YEAR ( TODAY () ) && MAX ( 'calendar'[month] ) <= MONTH ( TODAY () ), CumulativeYTD )
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Dillinger
I create calendar table as below
calendar = ADDCOLUMNS ( CALENDARAUTO (), "mm/yy", FORMAT ( [Date], "mm/yy" ), "yy/mm", FORMAT ( [Date], "yy/mm" ), "year", YEAR ( [Date] ), "month", MONTH ( [Date] ) )
Create a measure
Measure = VAR CumulativeYTD = TOTALYTD ( SUM ( 'Table'[value] ), 'calendar'[Date], FILTER ( ALL ( 'calendar' ), 'calendar'[Date] <= TODAY () ) ) RETURN IF ( MAX ( 'calendar'[year] ) = YEAR ( TODAY () ) && MAX ( 'calendar'[month] ) <= MONTH ( TODAY () ), CumulativeYTD )
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for the solution! Works perfectly. Your help is dearly appreciated.
For others, do note the format of the solution's fields [Month] and [Year]. Integer may be only compared against integer, not string.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |