Visualization to Latest Quarter with Condition

[Anonymous]

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

[v-deddai1-msft]

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]

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]

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

[Anonymous]

Hi @v-deddai1-msft ,

Error is still there. Could you please assist?

Best regards,

Chulendra

[v-deddai1-msft]

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]

Hi @v-deddai1-msft ,

problem still exists in the measure

Thanks and regards,

Chulendra

[v-deddai1-msft]

Hi @Anonymous ,

Show me your pbix file.

Best Regards,

Dedmon Dai

[Anonymous]

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

[v-deddai1-msft · Accepted solution]

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

[amitchandak]

@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]

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.