Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

time intelligence on count rather than sum

Hello 

 

what would be the DAX approach for running total / period average / rolling average if the column values need counting and time is custom (week numbers)? 

 

I have dates filled in a column and i want them to aggregate on week numbers. 

 

please help 

 

 

Need Help
  • speedramps's avatar
    speedramps
    4 years ago

    In Power Query create a calendar table with contigous dates.

    see https://www.youtube.com/watch?v=BtYn1hfdSAM

     

    Add a Start of Week column using the Date.StartOfWeek command.

    see  https://docs.microsoft.com/en-us/powerquery-m/date-startofweek

     

    Create dax measure:- 


    TotalSales = SUM(Sales[Amount])

    Running sales =
    VAR maxdateforperiod = MAX('Calendar'[Date])
    RETURN
    CALCULATE(
    [TotalSales],
    'Calendar'[Date] <= maxdateforperiod)
     
    Create a table visual with Start of Week, TotalSales  and Running sales.
    see attached example 
     
     

    Thanks for reaching out for help.

    I have helped you, now please help me by giving kudos.

    Remeber we are unpaid volunteers.

    Click the thumbs up and accept as solution button. 

    One question per ticket please. If you need to extend your request then please raise a new ticket.

    You will get a quicker response and each volnteer solver will get the kudos they deserve. Thank you !

     

     

     

1 Reply

  • speedramps's avatar
    speedramps
    Super User
    4 years ago

    In Power Query create a calendar table with contigous dates.

    see https://www.youtube.com/watch?v=BtYn1hfdSAM

     

    Add a Start of Week column using the Date.StartOfWeek command.

    see  https://docs.microsoft.com/en-us/powerquery-m/date-startofweek

     

    Create dax measure:- 


    TotalSales = SUM(Sales[Amount])

    Running sales =
    VAR maxdateforperiod = MAX('Calendar'[Date])
    RETURN
    CALCULATE(
    [TotalSales],
    'Calendar'[Date] <= maxdateforperiod)
     
    Create a table visual with Start of Week, TotalSales  and Running sales.
    see attached example 
     
     

    Thanks for reaching out for help.

    I have helped you, now please help me by giving kudos.

    Remeber we are unpaid volunteers.

    Click the thumbs up and accept as solution button. 

    One question per ticket please. If you need to extend your request then please raise a new ticket.

    You will get a quicker response and each volnteer solver will get the kudos they deserve. Thank you !