Forum Discussion
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 show all months up to the selected month. There is a MonthIndex (numeric) column for each fiscal period (which is text). There are no actual dates in the dataset.
If the user selects Fiscal Period "03", I would want to filter the table where MonthIndex is <= 3. I thought a measure would allow me to flag a 1 or 0 if it meets this condition, then I could just do a visual level filter on the filter pane for this visual. I tried SELECTEDVALUE on Fiscal Period, but its row context, so when I add the measure it just shows the Fiscal Period for each row.
Any help is greatly appreciated.
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.
3 Replies
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))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.Hi,
Share the download link of the PBI file. Also, from which month does your FY start?
