Forum Discussion

project101's avatar
project101
Frequent Visitor
4 years ago

Running Totals in Power Query with slicer

There are several very fast calculation methods through m - query as List.Generate and in 

dax language with measures

CALCULATE(
    SUM('sales'[val]),
    FILTER(
        ALLSELECTED('sales'[period]),
        ISONORAFTER('sales'[period], MAX('sales'[period]), DESC)
    )

 

The  problem is to   add a sample filter in slicer with dates

the expression of run total Not recalculated as it is calculated in Dax
In fact the query should  recalculate the filter    and only then calculate

List.Generate , List.Sum or List.FirstN  for the column

 

#"Added Index" = Table.AddIndexColumn(#"**Prior Step**", "Index", 0, 1)

#"Added Running Total" = Table.AddColumn(#"Added Index", "Running Total, each List.Sum(List.FirstN(#"Added Index"[**Column to Add Values From**],[Index]+1)))

I assume there is a possibility through m - query

General Comment

3 Replies

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, project101 

     

    When you filter the data in a power query, once the previous steps are calculated, the later steps are always based on the results of the previous calculations. power query does not check all calculations forever like measures do, it is more like what the calculated columns do. If you wish to recalculate, then you re-add the index and run the total column.

     

     

    Hope this helps.

     

    Best Regards,
    Community Support Team _ Zeon Zheng
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, project101 

     

    May I ask if your problem has been solved?  Is the above post helpful to you?

    If  it does, could you please mark the post which help as Answered? It will help the others in the community find the solution easily if they face the same problem with you.  Thank you.

     

     

    Best Regards,
    Community Support Team _ Zeon Zheng
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.