Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Incremental into cumulative

Hi   I have been trying solutions but i just don't get it I want to do the following on Power BI   Like a sum with condition on date and on the the same product x lob I tried TotalYTD but...
  • v-robertq-msft's avatar
    v-robertq-msft
    4 years ago

    Hi, Anonymous 

    According to your description, I can roughly understand your requirement, you want to sum the "amount" if it's the same product and Lob for all valuation date anterior to the one in the line and create a slicer to filter the data based on the date column, right? I think you can create a measure to use the AllSelected() function instead of All() like this to achieve your requirement:

    Cumulative Amount 1 =

CALCULATE(SUM('Table'[Amount]),FILTER(ALLSELECTED('Table'),[LoB]=MAX('Table'[LoB])&&[Valuation Date]<=MAX('Table'[Valuation Date])))

    And you can get what you want, like this:

     

    You can download my test pbix file below

    For more info about the Allselected() function in DAX, please refer to this link:

    https://docs.microsoft.com/en-us/dax/allselected-function-dax

     

    Thank you very much!

     

    Best Regards,

    Community Support Team _Robert Qin

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
4 years ago

Anonymous See if this helps: S Excel to DAX Translation - Microsoft Power BI Community

 

Otherwise, Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    OK so i found this

     

    Cumulative Total =
    CALCULATE (
    SUM(Sheet1[Amount]),
    FILTER (
    ALL( Sheet1),
    Sheet1[Valuation Date] <= EARLIEST ( Sheet1[Valuation Date])))
     
    Which give me the cumulative sum by valuation date but i cannot add filter.
    Can someone help