Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Cumulative chart

Hi all,   I am new to PowerBI tool and I just wanted to test it for automation of the standard test that I do normally in Excel.      CURRENT EXCEL SOLUTION   It's quite simple - it's a chart ...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi Anonymous,

     

    Change the relationshiop between date table and the 2LINE ITEMS + data_from_HEADER table to single. And create the measure as below.

     

    Measure 3 = 
var m =CALCULATE(MAX('2LINE ITEMS + data_from_HEADER'[Yearmonth]),ALL('2LINE ITEMS + data_from_HEADER'))
VAR re =
    CALCULATE(SUM('2LINE ITEMS + data_from_HEADER'[VALUE]),FILTER(ALLEXCEPT('2LINE ITEMS + data_from_HEADER','2LINE ITEMS + data_from_HEADER'[SALESMAN]),'2LINE ITEMS + data_from_HEADER'[Yearmonth]
            <= MAX('CALENDAR'[YearMonth])))
RETURN
   IF (
        MAX ( 'CALENDAR'[YearMonth] ) > m,
        BLANK (),
        IF ( ISBLANK ( re ), 0, re )
    )

    Then we can get the result same as yours.

     

     

     

    Also please find the pbix as attached.

     

    Regards,

    Frank

v-frfei-msft's avatar
v-frfei-msft
Community Support
7 years ago

Hi Anonymous,

 

Does that make sense? If so, kindly mark my answer as a solution to close the case.


Regards,
Frank

v-frfei-msft's avatar
v-frfei-msft
Community Support
7 years ago

Hi Anonymous,

 

Change the relationshiop between date table and the 2LINE ITEMS + data_from_HEADER table to single. And create the measure as below.

 

Measure 3 = 
var m =CALCULATE(MAX('2LINE ITEMS + data_from_HEADER'[Yearmonth]),ALL('2LINE ITEMS + data_from_HEADER'))
VAR re =
    CALCULATE(SUM('2LINE ITEMS + data_from_HEADER'[VALUE]),FILTER(ALLEXCEPT('2LINE ITEMS + data_from_HEADER','2LINE ITEMS + data_from_HEADER'[SALESMAN]),'2LINE ITEMS + data_from_HEADER'[Yearmonth]
            <= MAX('CALENDAR'[YearMonth])))
RETURN
   IF (
        MAX ( 'CALENDAR'[YearMonth] ) > m,
        BLANK (),
        IF ( ISBLANK ( re ), 0, re )
    )

Then we can get the result same as yours.

 

 

 

Also please find the pbix as attached.

 

Regards,

Frank

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    This is excellent!

     

    Thank you