Forum Discussion

bitraveldepart's avatar
bitraveldepart
Frequent Visitor
7 years ago
Solved

Incremental Values/ group by Relative week

Hi,     I'd like to see the incremental Pax number from the beginning of the year till now and compare the rate of target. So I have my Pax number for each booking record with date, relative week...
  • HotChilli's avatar
    HotChilli
    7 years ago

    Create a summary table, grouping by week and SUM the passengers

    sumtab = SUMMARIZE(TourPassengers, 
     TourPassengers[RelativeWeek],  
     "total Pax", SUM(TourPassengers[Pax]))

    Then add a calculated column for cumulative totals

    CumulPax = CALCULATE ( SUM(sumtab[total Pax]),
    			FILTER (  
    			ALL ( sumtab ),
        		sumtab[RelativeWeek] <= EARLIER ( sumtab[RelativeWeek])))
HotChilli's avatar
HotChilli
Community Champion
7 years ago

Create a summary table, grouping by week and SUM the passengers

sumtab = SUMMARIZE(TourPassengers, 
     TourPassengers[RelativeWeek],  
     "total Pax", SUM(TourPassengers[Pax]))

Then add a calculated column for cumulative totals

CumulPax = CALCULATE ( SUM(sumtab[total Pax]),
    			FILTER (  
    			ALL ( sumtab ),
        		sumtab[RelativeWeek] <= EARLIER ( sumtab[RelativeWeek])))
bitraveldepart's avatar
bitraveldepart
Frequent Visitor
7 years ago

Hi,

 

My [Relative Week] is a column not meature. Therefore it doesn't recognize the column from the table.

Does it make sense?

 

Thanks,

  • HotChilli's avatar
    HotChilli
    Community Champion
    7 years ago

    I think that's just a syntax error.  You'll need to change the code i submitted so that it works with your tables/columns e.g my sample table is 'RelativeWeek' but yours should have a space in there

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi bitraveldepart

    Tested with HotChilli's solution, it turns out to be correct, could you try and ask me if there is any other question?

     

     

    Best Regards

    Maggie