cancel
Showing results for
Did you mean:

## Year 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.

1 ACCEPTED SOLUTION
Super User IV

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"))
``````

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://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...

Proud to be a Super User!

8 REPLIES 8
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.

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Super User IV

@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 =
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...

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

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 =
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...

Super User IV

@ashleylinkewich - FTR, I was not necessarily advocating the T"I" route. Sample data and expected output are generally the shortest path to victory.

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

@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

Super User IV

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Super User IV

@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

https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...

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

https://youtu.be/yPQ9UV37LOU

Proud to be a Super User!

Super User IV

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"))
``````

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://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...

Proud to be a Super User!

Announcements