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, pre...
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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi v-deddai1-msft ,

 

Error is still there. Could you please assist?

 

Best regards,

Chulendra

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

Hi Anonymous ,

 

Please 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,
                Dim_Date[Q] = quartartoday - 1
                    && YEAR ( Dim_Date[Date] ) = YEAR ( TODAY () )
            )
        )
    )
RETURN
    IF (
        monthafterlastquarter >= 1,
        CALCULATE (
            [Your Measure],
            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 ,

     

    problem still exists in the measure

     

    Thanks and regards,

    Chulendra

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

      Hi Anonymous ,

       

      Show me your pbix file.

       

      Best Regards,

      Dedmon Dai

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi v-deddai1-msft ,

         

        Here is  pbix file. I created Dim_Date1 table, as I already have Dim_Data table. Inside Dim_Date1 table I created the measure called test. I have placed the visual in the file. Appreciate, if you could add the logic to existing Dim_Date table.

         

        Thanks and regards,

        Chulendra