Forum Discussion

wlknsn's avatar
wlknsn
Helper I
7 years ago
Solved

Best Practice: Extrapolation/Forecasting

Before I starte waisting hours on finding the best practise on this topic, I though I might just ask given the question is pretty simple.

 

Let's say you have a model with the following data:

Customer | Monthlyly fee | Start Date | End Data

A | 100 | 01/06/2019 | 30/11/2019

B | 125  | 01/06/2019 | 31/12/2019

C | 75 | | 01/06/2019 | 31/10/2019

 

How can I get the monthly total (sum) values in a table to analyze this? Do you generate the monthly values first in PowerQuery of can this be done on the fly using DAX?

Need Help
  • TeigeGao's avatar
    TeigeGao
    7 years ago

    Hi wlknsn ,

    In your scenario, I would use the following DAX query to do that:

    Result =
CALCULATE (
    SUM ( Data[Monthly fee] ),
    FILTER (
        ALL ( Data ),
        Data[Start Date] <= MIN ( 'Date'[Date] )
            && Data[End Date] >= MAX ( 'Date'[Date] )
    )
)

    The result will like below:

    Best Regards,

    Teige

4 Replies

  • TeigeGao's avatar
    TeigeGao
    Solution Sage
    7 years ago

    Hi wlknsn ,

    In your scenario, I would use the following DAX query to do that:

    Result =
CALCULATE (
    SUM ( Data[Monthly fee] ),
    FILTER (
        ALL ( Data ),
        Data[Start Date] <= MIN ( 'Date'[Date] )
            && Data[End Date] >= MAX ( 'Date'[Date] )
    )
)

    The result will like below:

    Best Regards,

    Teige

  • Mariusz's avatar
    Mariusz
    Community Champion
    7 years ago

    Hi wlknsn 

    No need to pre aggregate data if your table is not massive, I would try DAX and if performance is not satisfactory then you can consider aggregating yuor data.

     

    Regards,
    Mariusz

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

    • wlknsn's avatar
      wlknsn
      Helper I
      7 years ago

      Is there an easy way to do convert that data into monthly records in powerquery?

      • Mariusz's avatar
        Mariusz
        Community Champion
        7 years ago

        Hi wlknsn 

        You can use "Group By" its fairly simple  

        Thanks

        Mariusz