Forum Discussion

rajanimaddala's avatar
rajanimaddala
Helper II
6 years ago
Solved

DAX YTD Total

Hi,   I have Month slicer. And a measure Total Cases = COUNTAX(FILTER(Cases, Cases[Source] = "Case"), Cases[IdNo]) I have a clustered column chart which should show TotalCases YTD by Subject   ...
Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    You can use time intelligence. Datesytd or totalytd. With a date calendar

    YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

    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/

rajanimaddala's avatar
rajanimaddala
Helper II
6 years ago
Hi Amitchanda
Thanks for the reply. My FY starts from Aug to Jul
What could be my DAX with my FY
What happens with the slivers?
Thanks
amitchandak's avatar
amitchandak
Super User
6 years ago

rajanimaddala 

You have a formula like this

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"7/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"7/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"7/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"7/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"7/31"))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

 

Also, check this calendar table from Apr-Mar and change it to Aug-Jul. Look at year start date, year-end date and how finanical months are calculated

https://www.dropbox.com/s/wrcyk5j66corvjg/Apr2Mar-Cal.pbix?dl=0