cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AKB_2K19
Frequent Visitor

Rolling sum count of number of entries for 4 quarters based on year selection

Hello All,

 

I got a table called "OverallData" which contains entries for past 5 years. Table structure is as follows

 

NameDateStatusProgram Name
ABC1/10/2015Started2-7 Months
XYZ5/14/2016Closed4-12 Months programs
AAA2/22/2018In ProgressEmployee Engagement
AN8/16/2019In ProgressExecutive program
MYX9/20/2022 Retirement program

 

I want to create a table with Program Name as Row headers and Status as column headers. But I want to showcase rolling sum when I select the quarter year slicer i.e. On selecting Q4 2022 from slicer it should showcase me the values till start of Q4 2021 and so on. I do have a separate date table "dimDate" which contain dates from 2015 till date. Can someone help me with appropriate DAX for this scenario as the below one doesn't seem to giving me the correct output.

 

 

rollSum = CALCULATE(COUNT(OverallData[Status]),DATEADD(dimDate[Date],-4,QUARTER))

 

 

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

Hi @AKB_2K19 ,

 

 I do have a separate date table "dimDate" which contain dates from 2015 till date. 

For your case, we just need a Quarter- Year Column  and an additional column for sorting like:

Quarter Slicer = 
var _q={"Q1","Q2","Q3","Q4"}
var _y=VALUES(OverallData[Date].[Year])
return  SELECTCOLUMNS(CROSSJOIN(_q,_y) ,"Qua - Year", [Value] & " "&[Date].[Year], "Index", [Date].[Year]*100+RIGHT([Value],1))

Eyelyn9_0-1667568547333.png

 

Then please create a measure :

rollSum =
CALCULATE (
    COUNT ( OverallData[Status] ),
    FILTER (
        'OverallData',
        YEAR ( [Date] ) * 100
            + QUARTER ( [Date] )
            >= SELECTEDVALUE ( 'Quarter Slicer'[Index] ) - 100
            && YEAR ( [Date] ) * 100
                + QUARTER ( [Date] )
                <= SELECTEDVALUE ( 'Quarter Slicer'[Index] )
    )
)

Output:

Eyelyn9_1-1667568643373.png

 

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-yinliw-msft
Community Support
Community Support

Hi @AKB_2K19 ,

 

 I do have a separate date table "dimDate" which contain dates from 2015 till date. 

For your case, we just need a Quarter- Year Column  and an additional column for sorting like:

Quarter Slicer = 
var _q={"Q1","Q2","Q3","Q4"}
var _y=VALUES(OverallData[Date].[Year])
return  SELECTCOLUMNS(CROSSJOIN(_q,_y) ,"Qua - Year", [Value] & " "&[Date].[Year], "Index", [Date].[Year]*100+RIGHT([Value],1))

Eyelyn9_0-1667568547333.png

 

Then please create a measure :

rollSum =
CALCULATE (
    COUNT ( OverallData[Status] ),
    FILTER (
        'OverallData',
        YEAR ( [Date] ) * 100
            + QUARTER ( [Date] )
            >= SELECTEDVALUE ( 'Quarter Slicer'[Index] ) - 100
            && YEAR ( [Date] ) * 100
                + QUARTER ( [Date] )
                <= SELECTEDVALUE ( 'Quarter Slicer'[Index] )
    )
)

Output:

Eyelyn9_1-1667568643373.png

 

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.