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

 

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

)

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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  

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

)