Forum Discussion
Advocate IIYear To Date Values When Single Month Slicer Selected
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!
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-e3174b39f38aTo 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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.
8 Replies
- amitchandak
Super User
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-e3174b39f38aTo 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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos. - Greg_Deckler
Community Champion
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-TITHW/m-p/434008
ashleylinkewichHi 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!
- amitchandak
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-e3174b39f38aTo handle Budger in a different manner if needed
- v-diye-msft
Community Support
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.
