Forum Discussion

gilmara's avatar
gilmara
Frequent Visitor
3 years ago
Solved

YTD Custom Slicer

Perhaps I'm missing something simple, or just can't quite get the search terms right.  I have a report with a fiscal year and fiscal month slicer. When a fiscal month is selected, we would like to s...
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    3 years ago

    Hi, gilmara ;

    We need add another new table about Period.

    Period = VALUES('Table'[FiscalPeriod])

    Then add a flag measure.

    flag = 
IF(MAX('Table'[FiscalPeriod])<=MAX('Period'[FiscalPeriod]),1)

    Apply it into filter.

    The final show:


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak's avatar
amitchandak
Super User
3 years ago

gilmara , You can date using

 

date = date([Year], [month],1)

 

and then you can use date table and time intelligence

 

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

 

 

Otherwise have a table with Year, Month, and Year Month (YYYYMM )  joined to your table on YYYYMM , say Date 

 

YTD= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Month] <= Max('Date'[Month]) ))
LYTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Month] <= Max('Date'[Month])))

 

//Only year vs Year, not a level below

This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))