Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 filt...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

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

)

     

v-xuding-msft's avatar
v-xuding-msft
Community Support
6 years ago

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

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

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

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

)