Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Need help formatting data structure.

Hi so I have a data source that gives a sales persons numbers in this format: Date Sale amount 4/1/20 $100 6/1/20 $200 6/3/20 $200   I need to be able to format the data so it b...
Need Help
  • az38's avatar
    az38
    6 years ago

    Hi Anonymous 

    first create a calendar table

    Calendar = CALENDARAUTO()

    then you can create whether a calculated column

    Sales Amount (Cumulative Total) =
CALCULATE(SUM(Table[Sale amount]), FILTER(ALL(Table]), Table[Date] <= EARLIER(Calendar[Date])))

    or measure 

    Sales Amount (Cumulative Total) =
CALCULATE(SUM(Table[Sale amount]), FILTER(ALL(Table]), Table[Date] <= MAX(Calendar[Date])))

     for this calendar table

az38's avatar
az38
Community Champion
6 years ago

Hi Anonymous 

first create a calendar table

Calendar = CALENDARAUTO()

then you can create whether a calculated column

Sales Amount (Cumulative Total) =
CALCULATE(SUM(Table[Sale amount]), FILTER(ALL(Table]), Table[Date] <= EARLIER(Calendar[Date])))

or measure 

Sales Amount (Cumulative Total) =
CALCULATE(SUM(Table[Sale amount]), FILTER(ALL(Table]), Table[Date] <= MAX(Calendar[Date])))

 for this calendar table

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    This is very close with the measure but it is giving an unexpected result. It is assigning the total sum of that column to a every person. Not a cumulative total of each person for each month.

    az38