Cumulative Weekly Column Chart Report

[Anonymous]

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

[v-xuding-msft]

Hi @Anonymous ,

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.

[Anonymous]

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.

Week	CumulativeChGrpAcc
1	49
2	191
3	335
4	500
5	642
6	848
7	1072
8	1213	Should have stop at week 8
9	1213
10	1213
..	1213
..	1213
..	1213
52	1213

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

[Anonymous · 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])
		
	    )
	
    )

)

[Anonymous]

Hi @Anonymous 

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