Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi all!
I am looking to have a user select a month from the slicer and have the YTD value come up for each line in the P&L. I have created a measure that works well and summarises them perfectly as seen in the photo of the table below... The code is
Budget YTD = CALCULATE('**IBRCS FY21 Budget'[BudgetCalc], DATESYTD('**IBRCS FY21 Budget'[Month], "30/6")), to transform it into financial YTD rather than calendar.
However when I add that calc to my matrix, I want the user to be able to select the month and have the YTD value display... naturally if I select august it only shows the sum for August and not July + August.
Is there a way that I can have the YTD value in my matrix by only selecting the one month instead of multiple to summarise the values? I.e if they select September, having the Budget YTD display the sum of July + August + September.
Thanks in advance!
Solved! Go to Solution.
@ashleylinkewich , Please a calendar/Table table marked as date table
example
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Power BI — YTD
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...
Appreciate your Kudos.
If the above posts help, please kindly mark it as a answer to help others find it more quickly. thanks!
If not, please kindly elaborate more.
@ashleylinkewich - To use time "intelligence" functions you generally need to have a separate date table. If you do not want or have that, you should be able to do it using something like this:
Budget YTD =
VAR __Date = MAX('**IBRCS FY21 Budget'[Month])
VAR __Table =
ADDCOLUMNS(
FILTER(ALL('**IBRCS FY21 Budget'),[Month]<__Date),
"__BudgetCalc",[BudgetCalc]
)
RETURN
SUMX(__Table,[__BudgetCalc])
The important thing to note here is the use of ALL to break out of the current filter context imposed by the slicer. This assumes that [BudgetCalc] is a measure, which is what I am getting from your formula.
Also, there is a stanard quick measure built into Power BI, you click the ellipses for your column and select New quick measure and then Total YTD under Time "Intelligence". This has the form:
Value YTD =
IF(
ISFILTERED('Calendar'[Date]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
TOTALYTD(SUM('Table (19)'[Value]), 'Calendar'[Date].[Date])
)
You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000
Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...
Hi @Greg_Deckler , thanks for your help!
I would be happy to use a date table, however whenever I map it to my budget table, I get this error:
Which seems like a circular reference error, or otherwise? I've never seen it before.
I added and tried to modify your code slightly to find the maximum fiscal month number to only calculate for this financial year:
Budget YTD =
VAR __Date = MAX('**IBRCS FY21 Budget'[FiscalMonthNo])
VAR __Table =
ADDCOLUMNS(
FILTER(ALL('**IBRCS FY21 Budget'),[FiscalMonthNo]<__Date),
"__BudgetCalc",[BudgetCalc]
)
RETURN
SUMX(__Table,[__BudgetCalc])
When I add that to the table however, it seems like its summarising everything, regardless of the KPI group: Is there a way to separate them out or am I going about this the wrong way? Perhaps if I can get the date table to work that will provide the most straightforward solution...
Thanks again for your help!
@ashleylinkewich - FTR, I was not necessarily advocating the T"I" route. Sample data and expected output are generally the shortest path to victory.
@Greg_Deckler - totally, it seems like a bit of a nightmare. Love your blog though! Really clear.
Thanks again! I've managed to sort it by clearing up the problem with my date table
@ashleylinkewich Great!! 🙂
@ashleylinkewich , when you want to give date range what is need to calendar auto
you can use
Date = Calendar(date(2009,01,01), date(2015,12,31)
For the Rest you can use addcolumns or as new columns
Calendar -https://youtu.be/Qt0TM-4H09U
Power BI — YOY with or without time intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
To handle Budger in a different manner if needed
@ashleylinkewich , Please a calendar/Table table marked as date table
example
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Power BI — YTD
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...
Appreciate your Kudos.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
75 | |
74 | |
56 | |
45 |
User | Count |
---|---|
117 | |
105 | |
77 | |
66 | |
64 |