Year To Date Values When Single Month Slicer Selected

[ashleylinkewich]

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! 

[v-diye-msft]

Hi @ashleylinkewich 

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.

[Greg_Deckler]

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

[ashleylinkewich]

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! 

[Greg_Deckler]

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

[ashleylinkewich]

@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 

[Greg_Deckler]

@ashleylinkewich Great!! 🙂

[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 

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

[amitchandak · Accepted 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.