Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Trouble dividing two measures (one with a filter)

Hi,

 

Received help on this forum to get LY MTD number here but now have an issue with the comparison of this years month to date v. last years month to date. Here are my current tiles:

 

 

MTD Revenue Measure is =  Calculate(Sum(ItemSales[Total Sales]),DATESMTD(ItemSales[Date]))

 

LY MTD Revenue Measure is = CALCULATE(SUM(ItemSales[Total Sales]),DATESMTD(dateadd(ItemSales[Date],-12,MONTH)))

With a filter for the date: Relative Date filtering, show items when the value is in the last 1 days.

 

The Comp measure is currently = ([Total Sales Now]-[Last Years Sales])/[Last Years Sales]

and returns 1.05% but it should return (310,849.97 - 292,201.49) / 292,201.49 = 0.0638 or 6.38%

 

Using: (MTD-[MTD Last Year Second BI ATtempt])/[MTD Last Year Second BI ATtempt] returns -24.96%

 

Hoping someone can help me with the formula.

Need Help

1 Reply

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    HI Anonymous 

    I have test on my side, but not reproduce the issue. If possible, you could please share your sample pbix file for us have a test.

    and for your case, DATESMTD is a Time Intelligence Function,

    you'd better add a dim date table and then use this dim date table in the dax formula and visual.

    For example:

    MTD Revenue Measure is =  Calculate(Sum(ItemSales[Total Sales]),DATESMTD(Date[Date]))

    LY MTD Revenue Measure is = CALCULATE(SUM(ItemSales[Total Sales]),DATESMTD(dateadd(Date[Date],-12,MONTH)))

    and use Date[Date] in a slicer:  Relative Date filtering

    https://radacad.com/do-you-need-a-date-dimension

     

    Regards,

    Lin