Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Visualization to Latest Quarter with Condition

I have a visual that needs to be filtered to the latest quarter with a condition as follows.

 

If the difference between current date and last date of the previous quarter is less than a month, previous quarter and the remaining days in this quarter should be considered. Otherwise current quarter should be considered.

 

Could you please help me on how to achieve this task? Thanks in advance

Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    Hi Anonymous ,

     

    Please use :

     

    test =
VAR monthafterlastquarter =
    MOD ( MONTH ( TODAY () ), 3 )
VAR quartartoday =
    QUARTER ( TODAY () )
VAR firstdayoflastquarter =
    IF (
        quartartoday = 1,
        CALCULATE (
            MIN ( Dim_Date1[Date] ),
            FILTER (
                Dim_Date1,
                Dim_Date1[Q] = 4
                    && YEAR ( Dim_Date1[Date] )
                        = YEAR ( TODAY () ) - 1
            )
        ),
        CALCULATE (
            MIN ( Dim_Date1[Date] ),
            FILTER (
                Dim_Date1,
                Dim_Date1[Q] = quartartoday - 1
                    && YEAR ( Dim_Date1[Date] ) = YEAR ( TODAY () )
            )
        )
    )
RETURN
    IF (
        monthafterlastquarter >= 1,
        CALCULATE (
            [utilallocvariation],
            FILTER (
                Dim_Date1,
                Dim_Date1[Q] = quartartoday
                    && YEAR ( Dim_Date1[Date] ) = YEAR ( TODAY () )
            )
        ),
        CALCULATE (
            [utilallocvariation],
            FILTER (
                Dim_Date1,
                Dim_Date1[Date] >= firstdayoflastquarter
                    && Dim_Date1[Date] <= TODAY ()
            )
        )
    )

     

     

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

     

    Best Regards,

    Dedmon Dai

11 Replies

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

    Anonymous , a measure like

    measure  =

    var _month = mod(month(today(),3)

    var _end = eomonth(date(year(today()), month(today()) -1*_month, 1),0)

    var _diff =datediff(_month, _end, month)

    return

    if(_diff>1, CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
    , CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER))) )

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi amitchandak ,

       

      Thank you very much for the reply. I am not getting expected results. I think problem is with false term in the if clause.

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous ,

     

    First, you need to create a dim_date table in your date model:

     

    Dim_Date = ADDCOLUMNS(CALENDAR(DATE(2021,1,1),DATE(2021,12,31)),"Month",MONTH([Date]),"Q",FORMAT([Date],"q"))

     

    Then you can use the following measure :

    Measure =
VAR monthafterlastquarter =
    MOD ( MONTH ( TODAY () ), 3 )
VAR quartartoday =
    QUARTER ( TODAY () )
VAR firstdayoflastquarter =
    IF (
        quartartoday = 1,
        CALCULATE (
            MIN ( Dim_Date[Date] ),
            FILTER (
                Dim_Date,
                Dim_Date[Q] = 4
                    && YEAR ( Dim_Date[Date] )
                        = YEAR ( TODAY () ) - 1
            )
        ),
        CALCULATE (
            MIN ( Dim_Date[Date] ),
            FILTER (
                Dim_Date,
                FILTER (
                    Dim_Date,
                    Dim_Date[Q] = quartartoday - 1
                        && YEAR ( Dim_Date[Date] ) = YEAR ( TODAY () )
                )
            )
        )
    )
RETURN
    IF (
        monthafterlastquarter >= 1,
        CALCULATE (
            [Your Measure],
            FILTER (
                Dim_Date,
                FILTER (
                    Dim_Date,
                    Dim_Date[Q] = quartartoday
                        && YEAR ( Dim_Date[Date] ) = YEAR ( TODAY () )
                )
            ),
            CALCULATE (
                [Your Measure],
                FILTER (
                    Dim_Date,
                    Dim_Date[Date] >= firstdayoflastquarter
                        && Dim_Date[Date] <= TODAY ()
                )
            )
        )
    )

     

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

     

    Best Regards,

    Dedmon Dai

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi v-deddai1-msft ,

       

      Thank you very much for the reply. I am getting following error.

       

      The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

       

      Could you please check?

      • v-deddai1-msft's avatar
        v-deddai1-msft
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi Anonymous ,

         

        Would you please try to change the dim_date calculated table to:

         

        Dim_Date = ADDCOLUMNS(CALENDAR(DATE(2021,1,1),DATE(2021,12,31)),"Month",MONTH([Date]),"Q",QUARTER([Date]))

         

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

         

        Best Regards,

        Dedmon Dai