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,

 

Could you please share your sample data and excepted result to me. You can upload your files to onedrive and share the link here.

 

Reagrads,

Frank

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

    Hi Anonymous,

     

    Please check the following steps as below.

     

    1. Create a calculated column.

     

    Yearmonth = YEAR('2LINE ITEMS + data_from_HEADER'[SALESDATE])*100+MONTH('2LINE ITEMS + data_from_HEADER'[SALESDATE])

    2. To create a measure as below.

     

    Measure = 
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 ( '2LINE ITEMS + data_from_HEADER'[Yearmonth] )
    )
)

     

    For more details, please check 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