Forum Discussion

paulsnet1986's avatar
paulsnet1986
Icon for Helper I rankHelper I
3 years ago
Solved

Need Help With Time-Series Measures: One and Two Months Prior Sales Based on Selected Value

Hi, I am really struggling with this one: I need to create a margin report by product in that, when a given month is selected, the measures will give me the sum of the margins for each product  for t...
Need Help
  • Ritaf1983's avatar
    Ritaf1983
    3 years ago

    Hi paulsnet1986 

    You can use these measures :

    1. current month margin = sum('Table'[Margin])
    2.One Month Prior = CALCULATE([current month margin],DATEADD('Table'[Invoice],-1,MONTH))+0
    3. Two Month Prior = CALCULATE([current month margin],DATEADD('Table'[Invoice],-2,MONTH))+0
     

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

  • Ritaf1983's avatar
    Ritaf1983
    3 years ago

    Hi  1. Try to check your steps near the file I linked in the first reply.

    2. If it still not working:
    - Create a date table.

    - Create a relationship between it and your invoices table

    - Mark this table as a date table

    - Use this table for the formulas modify them to :

    current month margin = sum('Table'[Margin]) 
     
    One Month Prior = CALCULATE([current month margin],DATEADD('Dates_table'[Date],-1,MONTH))+0
     Two Month Prior = CALCULATE([current month margin],DATEADD('Dates_table'[Date],-2,MONTH))+0
    -  Use the date from the date table in the slicer and your visual
    I recorded the screens of these actions You can download the mp4 from: Here 
    And link to a new pbix is : Here

     

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

     

    paulsnet1986

Ritaf1983's avatar
Ritaf1983
Icon for Super User rankSuper User
3 years ago

Hi paulsnet1986 

You can use these measures :

1. current month margin = sum('Table'[Margin])
2.One Month Prior = CALCULATE([current month margin],DATEADD('Table'[Invoice],-1,MONTH))+0
3. Two Month Prior = CALCULATE([current month margin],DATEADD('Table'[Invoice],-2,MONTH))+0
 

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

  • paulsnet1986's avatar
    paulsnet1986
    Icon for Helper I rankHelper I
    3 years ago

    Hi Ritaf1983 

    Thanks for your reply! I am getting the following error FUNCTION 'DATEADD" expects contiguous selection when the date column is not unique, has gaps, or contains a time portion.

    • Ritaf1983's avatar
      Ritaf1983
      Icon for Super User rankSuper User
      3 years ago

      Hi  1. Try to check your steps near the file I linked in the first reply.

      2. If it still not working:
      - Create a date table.

      - Create a relationship between it and your invoices table

      - Mark this table as a date table

      - Use this table for the formulas modify them to :

      current month margin = sum('Table'[Margin]) 
       
      One Month Prior = CALCULATE([current month margin],DATEADD('Dates_table'[Date],-1,MONTH))+0
       Two Month Prior = CALCULATE([current month margin],DATEADD('Dates_table'[Date],-2,MONTH))+0
      -  Use the date from the date table in the slicer and your visual
      I recorded the screens of these actions You can download the mp4 from: Here 
      And link to a new pbix is : Here

       

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

       

      paulsnet1986

      • paulsnet1986's avatar
        paulsnet1986
        Icon for Helper I rankHelper I
        3 years ago

        That solved it! Ritaf1983 THANK YOU SO MUCH!