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

 

Then I am trying to add a  new Measure in the Selection Table as follows

 

YTD/QTD/MTD Selection =
VAR TodayDate = TODAY()
VAR YearStart = CALCULATE(STARTOFYEAR('Pax Revenue'[UpliftDate]),YEAR('Pax Revenue'[UpliftDate])= YEAR(TodayDate))
VAR QuarterStart = CALCULATE(STARTOFQUARTER('Pax Revenue'[UpliftDate]),YEAR('Pax Revenue'[UpliftDate]) = YEAR(TodayDate), QUARTER('Pax Revenue'[UpliftDate]) = QUARTER(TodayDate))
VAR MonthStart = CALCULATE(STARTOFMONTH('Pax Revenue'[UpliftDate]),YEAR('Pax Revenue'[UpliftDate]) = YEAR(TodayDate),MONTH('Pax Revenue'[UpliftDate]) = MONTH(TodayDate))
VAR Result =
UNION(
ADDCOLUMNS(CALENDAR(YearStart,TodayDate),"Selection","YTD"),
ADDCOLUMNS(CALENDAR(QuarterStart,TodayDate),"Selection","QTD"),
ADDCOLUMNS(CALENDAR(MonthStart,TodayDate),"Selection","MTD")
)
RETURN Result
 
I am getting the error: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.
 
Please help
 
Veemal
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.

6 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    VeemalS , Please use Time intellignce with date table, ine directional join

     

    For measure slicer refer

     


    measure slicer
    https://www.youtube.com/watch?v=b9352Vxuj-M
    https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/500115
    https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slicer-parameter-table-pattern
    https://www.youtube.com/watch?v=vlnx7QUVYME

    calculation groups
    https://www.sqlbi.com/blog/marco/2020/07/15/creating-calculation-groups-in-power-bi-desktop/

     

     

    QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
    Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))

     

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

     

    MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
    last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

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

      Thanks for the solution. But with this one I will have to create mutiple measures for each time periods which is somthing not acceptable by the user. In fact I was able to resolve same as I had to switch to creation of New Table iso   a Measure.

  • 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
    • Anonymous's avatar
      Anonymous
      Not applicable
      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

    Hello,

     

    Thanks. The Calculation groups ( With Tabular Editor) resolved my issue.

     

    Brgds

     

    Veemal