Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Relative Date Calculations Using Variables

Hi All,

 

Trying to come up with a series of measures which could accomplish the following

1. Sum of Quantity of Previous 1 Month (from a given date) * weight factor (1.2)

2. Sum of Quanity of Previous 3 Months (from the same date) * weight factor (0.8)

3. Sum of Quantity of Previous 6 Months (from the same date) * weight factor (0.6)

4. Sum of Quantity of Previous 12 Months (from the same date)* weight factor (0.4)

5. Can Plot values from past 13 months with each month displaying value of Sum (Item 1. through Item 4.)

 

It seems I am able to calculate the values, but the graph is not displaying the correct values for all of the months.

 

Basically I am using a series of formulas like below and adding them together

QtyTotal1Month = 
Var CurrentDate = MIN('Date'[Date])
Return
CALCULATE(
                    'Date'[Total Qty] // This is a Sum of the Total Quantity
                        ,ALL('Date'[Date])
                        ,'Date'[Date] <=EOMONTH(CurrentDate,0),Date[Date]>EOMONTH(CurrentDate,-1))*12*0.1 // The -1 would then be replaced by -3,-6,-12 in other formulas

Resulting Graph - Stops at April even though technically my values for March would still not be 0.  Any help is appreciated. 

 

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Please Try this measure
    Note:- This measure for only last month for more use can change -1 to -3,-6, and more.
            

    Measure1 =
VAR P_ONEMONTH =
    CALCULATE (
        SUM ( 'Date'[Total Qty] ),
        DATEADD ( 'Date'[Date], -1, MONTH )
    )
RETURN
    P_ONEMONTH * 0.8

      :- When you put this measure into a line graph the first month does not appear because the first month did not contain data to show that month you can use this measure.

    Measure =
VAR P_OneMonth =
    CALCULATE (
        SUM ( 'Date'[Total Qty] ),
        DATEADD ( 'Date'[Date], -1, MONTH )
    )
VAR Result = one * 0.8
RETURN
    IF ( Result = BLANK (), 0, Result )

    If I did not get your requirement please correct me.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      This did not work - I will post more information as well as trying a different route to add dates.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hello Vairag99 - I now realize my problem is that I do not have specific rows for every month which contain rejects for that specific vendor.  When I try to apply a filter for the vendor on my PBI Visuals, it does not perform the calculations for those vendors for those months because no rows exist for that month and vendor even if the measure output is not 0.  PBI does not have any existing relationship for those months and that vendor.  I still do not know how to solve this problem