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 woul...
  • 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

amitchandak's avatar
amitchandak
Icon for Super User rankSuper 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
      Icon for Community Support rankCommunity 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