Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
ashleylinkewich
Advocate II
Advocate II

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. 

BudgetYTD.PNG

 

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. 

 

Report Snip.PNG

 

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! 

1 ACCEPTED SOLUTION
amitchandak
Super User
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-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.

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

View solution in original post

8 REPLIES 8
v-diye-msft
Community Support
Community Support

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.

 

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.
Greg_Deckler
Super User
Super User

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

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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: 

DateError.PNG

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

NewBudgetYTD.PNG

 

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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

@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!! 🙂



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

@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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
amitchandak
Super User
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-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.

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.