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 Pr...
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

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