Month, Quarter, Year selection

Hi All, I have a slicer Month, Quarter, Year and its value 1,2,3 respectivly. Also a date slicer. I want to show result all month and count when selecting Month, last month in the quarter(MAR Ye...

Need Help

v-deddai1-msft

5 years ago

Hi Anonymous ,

You can create a new date table:

Table = ADDCOLUMNS(CALENDAR(DATE(2019,1,1),DATE(2020,12,31)),"MONTHYr",FORMAT([Date],"yy-Mmm"),"year",FORMAT([Date],"yy"),"quarter",QUARTER([Date]))

Then you can use the following measure:

Selected Count =
VAR A =
    SUMMARIZE (
        ALL ( Aging_table ),
        Aging_table[MonthYr],
        "_COUNT",
            CALCULATE (
                DISTINCTCOUNT ( Aging_table[CusHOCode] ),
                Aging_table[ClassLega] = "L"
            )
    )
RETURN
    SWITCH (
        SELECTEDVALUE ( SelectedPeriod[Column1] ),
        "MONTH",
            SUMX (
                FILTER ( A, Aging_table[MonthYr] = MAX ( Aging_table[MonthYr] ) ),
                [_COUNT]
            ),
        "QUARTER",
            IF (
                RIGHT ( MAX ( Aging_table[MonthYr] ), 3 ) IN { "Mar", "Jun", "Sep", "Dec" },
                VAR q =
                    CALCULATE (
                        MAX ( 'Table'[quarter] ),
                        FILTER ( 'Table', 'Table'[MONTHYr] = MAX ( Aging_table[MonthYr] ) )
                    )
                VAR qm =
                    CALCULATETABLE (
                        VALUES ( 'Table'[MONTHYr] ),
                        FILTER (
                            'Table',
                            'Table'[quarter] = q
                                && LEFT ( MAX ( Aging_table[MonthYr] ), 2 ) = LEFT ( 'Table'[MONTHYr], 2 )
                        )
                    )
                RETURN
                    SUMX ( FILTER ( A, Aging_table[MonthYr] IN qm ), [_COUNT] ),
                BLANK ()
            ),
        "YEAR",
            IF (
                RIGHT ( MAX ( Aging_table[MonthYr] ), 3 ) = "Dec",
                SUMX (
                    FILTER (
                        A,
                        LEFT ( Aging_table[MonthYr], 2 ) = LEFT ( MAX ( Aging_table[MonthYr] ), 2 )
                    ),
                    [_COUNT]
                ),
                BLANK ()
            ),
        SUMX (
            FILTER ( A, Aging_table[MonthYr] = MAX ( Aging_table[MonthYr] ) ),
            [_COUNT]
        )
    )