Forum Discussion

VeemalS's avatar
VeemalS
Icon for Helper I rankHelper I
4 years ago
Solved

YTD Slicer

Hello,   I am trying to create a YTD/QTD/MTD slicer but I am encountering an issue.   First I create a Table  Selection with only dates column that links to my Calendar Table ( Bi Directional Mod...
General Comment
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi VeemalS ,

     

    If you want to change your measure result by slicer, I suggest you can try calculation group by tabular.

    For reference: Creating Calculation Groups in Power BI Desktop

    From your screenshot, I see that there are different months in same year. Do you want all month in 2021 show same result when you select YTD? And Apr/May/Jun show same results in Qtr2, Jul show results in Qtr3, when you select QTD? I am confused about the rates, what is the calculation logic of thems?

    Please share a sample file with us and show us the result you want by screenshot.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

VeemalS's avatar
VeemalS
Icon for Helper I rankHelper I
4 years ago

This is the new DAX code:

CustomSelect =
VAR PaxRevDate = MAX('Pax Revenue'[UpliftDate])--Today()
VAR Dates_YTD = Calculate (STARTOFYEAR('Time'[PeriodDate]), YEAR('Time'[PeriodDate]) = YEAR(PaxRevDate))--DATESYTD('Pax Revenue'[UpliftDate])
VAR Dates_QTD = Calculate (STARTOFQUARTER('Time'[PeriodDate]),YEAR('Time'[PeriodDate]) = YEAR(PaxRevDate), QUARTER('Time'[PeriodDate]) = QUARTER(PaxRevDate))
--DATESQTD( 'Pax Revenue'[UpliftDate])
VAR Dates_MTD = Calculate (STARTOFQUARTER('Time'[PeriodDate]),YEAR('Time'[PeriodDate]) = YEAR(PaxRevDate), MONTH('Time'[PeriodDate]) = MONTH(PaxRevDate))
--DATESMTD('Pax Revenue'[UpliftDate])
RETURN
var FinalResult =
UNION(ADDCOLUMNS(
CALENDAR(Dates_YTD,PaxRevDate),
"Selection","YTD"
),
ADDCOLUMNS(
CALENDAR(Dates_QTD,PaxRevDate),
"Selection","QTD"
),
ADDCOLUMNS(
CALENDAR(Dates_MTD,PaxRevDate),
"Selection","MTD"
)
) return FinalResult
 
However does not solve all the problems as I am not able to filter on LY Measures. So for instance if I have a measure that has been filtered by SAMEPERIODLASTYEAR, the report does not bring any value. Same for DateAdd.
 
Any suggestion?
 
Thanks
 
Veemal