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

)