Month, Quarter, Year selection

[Anonymous]

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 Year, JUN Year...) when selecting Quarter and similarly Last month in the Year(DEC Year) when selecting Year.

I use below measure:

Selected Count = SWITCH([SelectedPeriod],

1, CALCULATE(DISTINCTCOUNT(Aging_table[CusHOCode]),Aging_table[ClassLegal]="L"),

2, CALCULATE(DISTINCTCOUNT(Aging_table[CusHOCode]),Aging_table[ClassLegal]="L", DATESQTD(DimDate[Date])),

3, CALCULATE(DISTINCTCOUNT(Aging_table[CusHOCode]),Aging_table[ClassLegal]="L",DATESYTD(DimDate[Date])),

CALCULATE(DISTINCTCOUNT(Aging_table[CusHOCode]),Aging_table[ClassLegal]="L"))

But the result is showing all month as below ( selected Quarter), it show only last month of the quarter.

Month	Count
Dec-20	9
Nov-20	4
Sep-20	8
Aug-20	7
Jul-20	5

and similarly for Year

Month	Count
Dec-20	108
Nov-20	104
Oct-20	100
Sep-20	100

Kindly help 

Regards

Samar

[v-deddai1-msft · Accepted solution]

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

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]

Many thanks Let me check and let you know.

[v-deddai1-msft]

Hi @Anonymous ,

Would you please explain more about your expected output? It doesn't meet your description in original post. There is only three distinct value in your sample data, how did you get 4 and 7 in your result? Please do more explanation.

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]

Hello,

I have Date slicer(MonthYr) and Period slicer(Month, Quarter and Year) values 1,2 and 3.

I want distinct count of CusHOCode  by (Month/Quarter/Year) where ClassLegal=L. 

Please find below Sample Data

Output: I want to show the result in one table visual based on Date and period slicer as below

If I selected Month in period slicer, it should show by Month distinct count of CusHOcode where classLegal=L as below:

If selected Quarter in period slicer, it should show only last month of quarter, but distinct count(CusHOCode) full quarter(all three months in the quarter) where classLegal=L. Output as below

If selected Year in period slicer, it should show only last month of the year, but distinct count(CusHOCode) full Year(all  months in the year) where classLegal=L. Output as below

I hope it will be clear

Regards

Samar

[Anonymous]

Requesting you all please reply

[Anonymous]

Dear Amit,

Sample Data

sorry sending like this

Output when selected Qurater Count CusHOCode where classLegal=L

Output when selected Year

Regards

Samar

[Anonymous]

Dear Amit,

I am sorry, since yesterday i am trying to send reply but giving error.

Regards

Samar

[amitchandak]

@Anonymous , if we display Datesqtd with month then display three months or more, datesqtd will not filter month, that will be filtered by date slicer , it will only take the last date from slicer for a Single/KPI value

But if you use any group time, it will show datesqtd for every available datapoint

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

also refer: https://community.powerbi.com/t5/Desktop/Required-custom-date-Slicer-Last-7-days-last-15-days-last-30/m-p/1284966#M561629