Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Rolling window

I need to calculate the cumulative of some values ​​while I am not sure why my result in Excel is different from my DAX query.

I can't share the actual data, but hope that the screenshots below would suffice.

The Power BI filters and the Pivottbale filters match each other exactly, checked them multiple times.

My DAX query is
Cumalative Cash Flow = CALCULATE( sum(Output_SSIS[Cash Flow]),filter(ALLSELECTED(Output_SSIS[Financial Year]),Output_SSIS[Financial Year]<=Max(Output_SSIS[Financial Year])))



And another screenshot
Cumalitive issue 2.png

  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    hi  Anonymous 

    Try these two way as below:

    1. set datatype of Cash Flow to Fixed decimal number

     

    2. Use a dim date table and create relationship with the fact table,

    Now use dim date table in the visual and measure as below

    Cumalative Cash Flow = CALCULATE( sum(Output_SSIS[Cash Flow]),filter(ALLSELECTED(Date),Date[Financial Year]<=Max(Date[Financial Year])))

     

    If you still have the problem, please share your sample pbix file for us have a test.

     

    Regards,

    Lin

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , to me power bi Cumulative  seems to be correct. that sum of all previous years

     

    // this what I see in excel

    This year Cash Flow = CALCULATE( sum(Output_SSIS[Cash Flow]),filter(ALLSELECTED(Output_SSIS[Financial Year]),Output_SSIS[Financial Year]=Max(Output_SSIS[Financial Year])))

     

    // this what I see in power bi

    Cumalative Cash Flow = CALCULATE( sum(Output_SSIS[Cash Flow]),filter(ALLSELECTED(Output_SSIS[Financial Year]),Output_SSIS[Financial Year]<=Max(Output_SSIS[Financial Year])))

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      @amitchandak

      Have added another screenshot in the original post which shows the difference more clearly in Power BI

      • v-lili6-msft's avatar
        v-lili6-msft
        Community Support
        6 years ago

        hi  Anonymous 

        Try these two way as below:

        1. set datatype of Cash Flow to Fixed decimal number

         

        2. Use a dim date table and create relationship with the fact table,

        Now use dim date table in the visual and measure as below

        Cumalative Cash Flow = CALCULATE( sum(Output_SSIS[Cash Flow]),filter(ALLSELECTED(Date),Date[Financial Year]<=Max(Date[Financial Year])))

         

        If you still have the problem, please share your sample pbix file for us have a test.

         

        Regards,

        Lin