Forum Discussion

JoaoTrindade's avatar
JoaoTrindade
Frequent Visitor
7 years ago
Solved

cumulative by group

Hi, I'm trying to do a cumulative sum by group. I've tried different solutions but none seems to work. I have a table with millions of entries of booking made and they all are grouped by channel gr...
Need Help
  • parry2k's avatar
    parry2k
    7 years ago

    JoaoTrindade here is the measure

     

    cumulative nights = 
CALCULATE(
    [total nights],
    FILTER(
        ALLSELECTED('gc_reports bookings'[book_date].[Date]),
        'gc_reports bookings'[book_date].[Date] <= MAX ('gc_reports bookings'[book_date].[Date])
    )
)

    also I noticed there is lot of both direction cross filter in your model whcih is a bad idea. anyhow that is seperate topic.

     

     

JoaoTrindade's avatar
JoaoTrindade
Frequent Visitor
7 years ago

parry2k  that goes to show you the state I'm in...  :) 

I selected the wrong file. Here it is

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    7 years ago

    JoaoTrindade here is the measure

     

    cumulative nights = 
CALCULATE(
    [total nights],
    FILTER(
        ALLSELECTED('gc_reports bookings'[book_date].[Date]),
        'gc_reports bookings'[book_date].[Date] <= MAX ('gc_reports bookings'[book_date].[Date])
    )
)

    also I noticed there is lot of both direction cross filter in your model whcih is a bad idea. anyhow that is seperate topic.

     

     

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    7 years ago

    JoaoTrindade I think this is what you are looking for, correcT?

     

  • JoaoTrindade's avatar
    JoaoTrindade
    Frequent Visitor
    7 years ago

    Absolutely! What's the magic trick?

  • JoaoTrindade's avatar
    JoaoTrindade
    Frequent Visitor
    7 years ago

    parry2k  Thanks a million Parry! This works to perfection.

  • JoaoTrindade's avatar
    JoaoTrindade
    Frequent Visitor
    7 years ago

    parry2k I was now looking in more detail at the data and the cumulative now it not accounting for the specific booking date selected. It is cumulating for all time. Why is that?