Forum Discussion

sw1331's avatar
sw1331
Regular Visitor
2 years ago
Solved

How do I create a trending downward chart?

Hello,

 

I have a table with monthly data and would like to plot it in a bar chart reducing the last value by 500 starting in 2024 April. 

 

e.g.

Feb 24 = 5000

Mar 24 = 4000

Apr 24 = 4000-500 = 3500

May 24 = 3500-500 = 2000

Jun 24 = 2000-500 = 1500

and so on...

 

The following DAX reduces it only from March 2024.  It does not go to April and May.  I need it to reduce by 500 from the calculated April value and the May value is calculated from the April value and so on.

 

Forecast =

VAR passThru =
CALCULATE([Total Items],
    DATEADD(Metrics[Yr-Mo], -1, MONTH)
) - 500

VAR curr_Month = MONTH(MAX('Metrics'[Yr-Mo]))
VAR curr_year = YEAR(MAX('Metrics'[Yr-Mo]))

RETURN

IF(AND(curr_year = 2024, curr_Month > 3),
    IF(passThru < 0, 0, passThru),
    [Total Items]
)
 
How would I change the above code to reduce it by 500 from the previous month?
 
Any help is appreciated.
Thank you.
Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago

    Do you have a calendar table for the X axis?

    and so on...

    until when?  Until it crosses the x axis?

     

7 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    Do you have a calendar table for the X axis?

    and so on...

    until when?  Until it crosses the x axis?

     

    • sw1331's avatar
      sw1331
      Regular Visitor
      2 years ago

      Can you please do a screenshot of the two tables?

       

      Thank you.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    Share the raw data from where you want to build that visual.  Also, should the reduction start in April of every year and cary on up until March of the following year?

    • sw1331's avatar
      sw1331
      Regular Visitor
      2 years ago

      Thank you for responding.  Let me play around with the 'solution' that was provided.  I'll share the dataset if needed if I get stuck in the next step.

       

      Thank you.

    • sw1331's avatar
      sw1331
      Regular Visitor
      2 years ago

      Hello Ashish - I am still struggling.  Here is my data.

      DateProductUnits
      1/2/2024A100
      1/10/2024B200
      1/11/2024C300
      2/22/2024A78
      2/21/2024B163
      2/26/2024C254
      3/22/2024A32
      3/21/2024B40
      3/26/2024C165

       

      I need to reduce the Units value by say 26 units (this will be a variable) starting April 1, 2024.  This needs to go on until May 31st.  I want to know the value on May 31st.  Since the reduction number is a variable, I can create a slider to see different scenarios.

       

      Thank you again.

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        2 years ago

        the data that you have shared and explanation do not support each other.  You mention May in the body of the message but there is no data of that month in the raw data.  Share meaningful data and show the expected result.