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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dcg38524
Helper III
Helper III

Cumulative Weekly Column Chart Report

Hello,

 

We have a Power BI weekly line column chart which collect cumulative data and seems to be working great.  There is one small issue with our reporting - everyweek we have to physically filter the current reporting week 😫.

 

Below is a example of the DAX code which produces the cumulative column chart, I have attempted to add a filter via. DAX but with no luck at all.

 

Any advice or suggestions with our dilemma would be greatly appreciated,

Thank you,

 

 

CumulativeChGrpAcc = 
CALCULATE([TotalChgGrpAcc],
    FILTER(ALLSELECTED(ChangeGrpAudit[Week]),
        ChangeGrpAudit[Week] <= MAX (ChangeGrpAudit[Week])
    )
)

 

TotalChgGrpAcc = CALCULATE(
    COUNTA('ChangeGrpAudit'[CHANGE_ID]),
        FILTER('ChangeGrpAudit', 'ChangeGrpAudit'[Year] = 2020
    )   
)

 

1 ACCEPTED SOLUTION

Hello Everyone,

 

Great News!!!!  I was able to find a solution to my problem via. Youtube(Cumulative Totals).  Being so new to DAX & Power BI I just didn't realize whe creating Cumulative values within a run chart the last value will progress until the end of the "X axis", which is what I was experiencing.

 

Below is a copy of the DAX code which addressed my individual problem and a link to the Youtube DAX Tutorial which explained Cumulative values and how to use Cumulative Totals Up To Specific Date.

 

NOTE: I had to alter my "Report Date" column by duplicating it into a separate column call "Date" changing the formatting to reflect date only.

 

Thanks everyone for your help

 

https://www.youtube.com/watch?v=JnhXyQ8eyuo 

 

Cumulative Test value = 
TotalChgGrpAcc = CALCULATE(
    COUNTA('ChangeGrpAudit'[CHANGE_ID]),
        FILTER('ChangeGrpAudit', 'ChangeGrpAudit'[Year] = 2020
    )
)


Reporting Week = WEEKNUM(TODAY(),1)


VAR LastWeekDate = CALCULATE(LASTDATE(ChangeGrpAudit[Date]), ALL(ChangeGrpAudit))

RETURN
IF( SELECTEDVALUE(ChangeGrpAudit[Week] ) > 'Key Measures'[Reporting Week], BLANK(),
	CALCULATE([TotalChgGrpAcc],
		FILTER(ALLSELECTED(ChangeGrpAudit[Week]),
        ChangeGrpAudit[Week] <= MAX (ChangeGrpAudit[Week])
		
	    )
	
    )

)

 

View solution in original post

4 REPLIES 4
v-xuding-msft
Community Support
Community Support

Hi @dcg38524 ,

As Jan suggested,  you could add a filter like WEEKNUM ( TODAY () ) to implement it. 

current_week =
CALCULATE (
    [TotalChgGrpAcc],
    FILTER (
        ALL ( 'ChangeGrpAudit'[Week], 'ChangeGrpAudit'[Year] ),
        'ChangeGrpAudit'[Week] = WEEKNUM ( TODAY () )
            && 'ChangeGrpAudit'[Year] = YEAR ( TODAY () )
    )
)

 

Best Regards,

Xue Ding

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

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

Thank you both, somehow I am still having trouble.  Not having problems achieving the desired cumulative run chart results but the final week 8 cumulative figure of " 1213 " continues to post until week 52.

 

I attempted to modifiy the DAX code many times with your suggestions and others really stomped at this point.

 

WeekCumulativeChGrpAcc
149 
2191 
3335 
4500 
5642 
6848 
71072 
81213 Should have stop at week 8
91213 
101213 
..1213 
..1213 
..1213 
521213 

 

 

 

Total 2020 CumulativeChGrpAcc = CALCULATE(
    COUNTA('ChangeGrpAudit'[CHANGE_ID]),
        FILTER('ChangeGrpAudit', 'ChangeGrpAudit'[Year] = 2020)  
    )


CumulativeChGrpAcc = 
CALCULATE([Total 2020 CumulativeChGrpAcc],
    FILTER(ALLSELECTED(ChangeGrpAudit[Week]),
        ChangeGrpAudit[Week] <= MAX (ChangeGrpAudit[Week])
        
    )
)

 

 

Hello Everyone,

 

Great News!!!!  I was able to find a solution to my problem via. Youtube(Cumulative Totals).  Being so new to DAX & Power BI I just didn't realize whe creating Cumulative values within a run chart the last value will progress until the end of the "X axis", which is what I was experiencing.

 

Below is a copy of the DAX code which addressed my individual problem and a link to the Youtube DAX Tutorial which explained Cumulative values and how to use Cumulative Totals Up To Specific Date.

 

NOTE: I had to alter my "Report Date" column by duplicating it into a separate column call "Date" changing the formatting to reflect date only.

 

Thanks everyone for your help

 

https://www.youtube.com/watch?v=JnhXyQ8eyuo 

 

Cumulative Test value = 
TotalChgGrpAcc = CALCULATE(
    COUNTA('ChangeGrpAudit'[CHANGE_ID]),
        FILTER('ChangeGrpAudit', 'ChangeGrpAudit'[Year] = 2020
    )
)


Reporting Week = WEEKNUM(TODAY(),1)


VAR LastWeekDate = CALCULATE(LASTDATE(ChangeGrpAudit[Date]), ALL(ChangeGrpAudit))

RETURN
IF( SELECTEDVALUE(ChangeGrpAudit[Week] ) > 'Key Measures'[Reporting Week], BLANK(),
	CALCULATE([TotalChgGrpAcc],
		FILTER(ALLSELECTED(ChangeGrpAudit[Week]),
        ChangeGrpAudit[Week] <= MAX (ChangeGrpAudit[Week])
		
	    )
	
    )

)

 

JustJan
Responsive Resident
Responsive Resident

Hi @dcg38524 

 

I assume there is a relation with the todays date for your Reporting Week. 

 

So you could you the TODAY() function to set you current week selection in the measure. 

 

WeekNumber = WEEKNUM(today()), or something like this
 

Jan  

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.