Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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 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.
MonthCount
Dec-209
Nov-204
Sep-208
Aug-207
Jul-205

and similarly for Year

MonthCount
Dec-20108
Nov-20104
Oct-20100
Sep-20100

 

Kindly help 

Regards

Samar

1 ACCEPTED SOLUTION
v-deddai1-msft
Community Support
Community Support

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

 

1.gif

 

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

 

View solution in original post

8 REPLIES 8
v-deddai1-msft
Community Support
Community Support

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

 

1.gif

 

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
Not applicable

Many thanks Let me check and let you know.

v-deddai1-msft
Community Support
Community Support

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
Not applicable

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

SamarK_0-1616651059490.png

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

SamarK_5-1616653198715.png

 

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

SamarK_1-1616651925410.png

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

SamarK_3-1616652796806.png

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

SamarK_4-1616652977076.png

I hope it will be clear

Regards

Samar

 

Anonymous
Not applicable

Requesting you all please reply

Anonymous
Not applicable

Dear Amit,

Sample Data

sorry sending like this

SamarK_0-1616566348527.png

Output when selected Qurater Count CusHOCode where classLegal=L

SamarK_1-1616566498672.png

Output when selected Year

SamarK_2-1616566562359.png

Regards

Samar

Anonymous
Not applicable

Dear Amit,

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

 

Regards

Samar

amitchandak
Super User
Super User

@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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors