Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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

Need Help
  • v-deddai1-msft's avatar
    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]
        )
    )

     

     

    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

     

8 Replies

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Icon for Community Support rankCommunity Support
    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]
        )
    )

     

     

    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

      Many thanks Let me check and let you know.

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Dear Amit,

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

     

    Regards

    Samar

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Dear Amit,

    Sample Data

    sorry sending like this

    Output when selected Qurater Count CusHOCode where classLegal=L

    Output when selected Year

    Regards

    Samar

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Requesting you all please reply

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

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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