Change yesterday vs day before, yesterday vs last week, yesterday vs last month

[satichun]

My Dataset is simple - I have 3 products (A,B,C) and I have dates from Jan-01-2020. I also have product-wise quantities for each day.

I need to show
1. % change in quantity daily (with reference to March-17-2020, previous day comparison is march-16-2020(quantity = 12) vs march-15-2020 (quantity = 13), % change = -7.69%

2. % change in quantity weekly (with reference to March-17-2020, previous week comparison week date is march-16-2020 vs March-09-2020)

3. % change in quantity monthly ((with reference to March-17-2020, previous month comparison date is march-16-2020 vs Feb-15-2020)

Please let me know if any info is needed. Thank you. % Quantity change.pbix (37.9 KB)

[Melissa]

Hi @satichun,

Welcome back.

So I’ve got a few questions for you.

1. when you say yesterday, do you mean from TODAY()? or from the Last Refresh Date? or MAX transaction date?
2. you are always comparing single days, correct?
3. how will you handle yesterday vs last month, when there is no date value in the prior month like for a 31st?

Here are some resources you might find helpful in the meantime:
https://forum.enterprisedna.co/t/the-best-time-comparison-function-dateadd/4927

Understanding the CALCULATE function - DAX in Power BI

Please see the above video to learn more. To ask any questions on the content covered in this tutorial please start a new topic within the forum. You may check this how-to guide for reference - How To Use The Enterprise DNA Support Forum

[BrianJ]

@satichun,

In addition to the resources that @Melissa suggested, you may find this thread helpful as well:

Today vs Yesterday

Hi, I am creating a POC for a mobile report which shows movements in the HRC (Steel) commodity price. I have a simple table of daily prices and would like to present cards showing today’s price, yesterdays price and the change between the 2. I have created a calculated column called WhatDayIsIt: “WhatDayIsIt = VAR today = TODAY() VAR rowdate = CALCULATE(SUM(‘DataTable’[Date])) RETURN IF(DATEDIFF(today,rowdate,DAY)=0,“Today”, IF(DATEDIFF(today,rowdate,DAY)=-1,“Yesterday”, “History”))” I…

• Brian

[satichun]

Thanks Melissa, the report should always work with reference to TODAY() (current day), but for computing the percentage it should take yesterday (TODAY()-1) vs day before (TODAY()-2)

I tried several variations(created measures with CALCULATE and FILTER, created columns, calculated tables that only will have yesterdays data and day before data - ran into circular references here), Created Date table ----nothing seems to work.

[hafizsultan]

Hi @satichun,

Seems file is corrupt. Can you please upload file again?

[satichun]

Strange - created it with March 2020 version.

Its also here - https://1drv.ms/u/s!AnzzvjwOAPJycWke1DPwIW1cgn8

[satichun]

Will check it out and come back Brian. Thank you.

[hafizsultan]

Hi @satichun,

My bad I think it is due to version mismatch that I got above error.

[Melissa]

Hi @satichun,

So I started to work of your first file.

1. And this didn’t contain a Dates table. It is important to realise that a Dates table is required and needs to be marked as a date table for any kind of time intelligence calc.
2. It also didn’t contain any avenues you’d explored - which can be beneficial in pointing you towards relevant content.

There’s a lot of content within Enterprise DNA resources and the forum, please use the search option in the right upper corner to find more relevant content. Here are a few resources on the Dates table:

https://forum.enterprisedna.co/t/importance-of-date-tables/4374

https://forum.enterprisedna.co/t/why-you-must-have-a-date-table/4926

.
Next I created a buch of measures to calculate the Dates, Quantity and %Change with this result:

.
Most required dates had a fixed offset, so to calculate them this pattern will do:

Last Week's date =
VAR ThisDate = SELECTEDVALUE( Dates[Date] )
RETURN

ThisDate -8

.
This is not the case however for the last month’s date:

Last Month's date = 
VAR Yesterday = [Yesterday's date]
VAR LastMonth = 
    CALCULATE( MAX( Dates[Date] ),
        DATEADD( FILTER( DATESMTD( Dates[Date] ), Dates[Date] = Yesterday ), -1, MONTH )
    )
RETURN

LastMonth

.
Once the dates are calculated the basic pattern to calculate the QTY becomes easy:

Yesterday's QTY = 
    CALCULATE( [QTY],
        Dates[Date] = [Yesterday's date]
    )

.
And finally the % change:

% change in quantity daily = 
IF( HASONEVALUE( Dates[Date] )  && NOT( ISBLANK( [Two days ago QTY] )),
    DIVIDE( [Two days ago QTY], [Yesterday's QTY] ) -1,
    BLANK()
)

.
I hope this is helpful. Here is your sample file.
eDNA - % Quantity change.pbix (66.4 KB)

[satichun]

Thank you Melissa. This works great. I had to extend the logic for business days and weekends ( if yesterday and 2 days ago fall on a weekend, those have to default to the previous Friday. After some struggle, I got those. If interested, this is what I came up with and this works

Yesterday's Qty = 
VAR ThisDate = SELECTEDVALUE( 'Dates'[Date] )
VAR Result =
    CALCULATE( SUM(Qty) ,
        'Dates'[Date] =  IF(WEEKDAY(ThisDate - 1) =1, ThisDate-3, IF(WEEKDAY(ThisDate-1) = 7, ThisDate-2, ThisDate-1))
    )
RETURN Result

[satichun]

I didnot know that for time intelligence to work a date table is essential, I believed a date field suffices- thank you for sharing. I sent a clean slate file, should have left the try and errors as-is.

The resources I had explored kind of revolved around the way you wrote the logic but had not used selectedvalue and hasonevalue - I need to understand those better.

[Melissa]

Great to hear you are on track.

I always feel we can learn the most from our mistakes. (I know I certainly do.) It’s a journey of discovery that will ultimately lead to a deeper understanding.

[EnterpriseDNA]

Hi @satichun, I hope that you are having a great experience using the Support Forum so far. We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Also, please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’. Thanks!

[sam.mckay]

Great job on tackling this concern @Melissa. Really impressed on how you’ve analysed the issue and provided solution that’s clear and concise.

[satichun]

Indeed a great job from @Melissa. Thank you again.

[satichun]

Hi @Melissa - If the selected data is 01-April-2020, then the last month date in this shows a null probably because of it looking back to February. Any idea how to address this. It should show me the last valid date, in this case that would be 29-Feb-2020. I tried using eomonth and that didnot work. Test Page>Measure called Last Months Date(Test) eDNA - % Quantity change.pbix (67.5 KB)

[Melissa]

Yeah I remenber asking you how you wanted to deal with that, no problem.
I’ve added a MonthOffset to your Dates table - this is what that M code looks like:

        InsertMonthOffset = Table.AddColumn(InsertWeekOffset, "MonthOffset", each 
          ((12 * Date.Year([Date])) +  Date.Month([Date])) - ((12 * Date.Year(Date.From(DateTime.FixedLocalNow()))) +  Date.Month(Date.From(DateTime.FixedLocalNow()))), Int64.Type)

.
Next I updated the measure and this is a perfect example for the new COALESCE function.
So if the LastMonth is blank the last day from the month before that month will be returned.

Last Month's date = 
VAR Yesterday = [Yesterday's date]
VAR LastMonth = 
    CALCULATE( MAX( Dates[Date] ),
        DATEADD( FILTER( DATESMTD( Dates[Date] ), Dates[Date] = Yesterday ), -1, MONTH )
    )
VAR LMifBlank = 
    CALCULATE( MAX( Dates[Date] ),
        FILTER( ALL( Dates ), Dates[MonthOffset] = -2 )
    )
RETURN

COALESCE( LastMonth, LMifBlank )

Here is the result.

.
I hope this was helpful.
eDNA - % Quantity change.pbix (66.8 KB)

[satichun]

Thank you @Melissa. Trying to learn and understand this better - why are we multiplying with 12 in the MonthOffset - the logic should return the same offset values if we exclude the multiplication right?

Also, would there be a DAX equivalent of the M code - I came up with something like this for a column and doesnot give me the same offset values Month_Offset = ((12Year(‘Dates’[Date]))+Month(‘Dates’[Date]))-(12Year(Today())+Month(Today()))

[Melissa]

@satichun you should always try to push this as far back to the source as you can. In this case that would mean Power Query. The reason for that is because if you add an equivalent using DAX you won’t be able to move it with your Dates table from one PBIX file to the next. You will essentially have to remember and recreate it every single time you require a MonthOffset.

Best practice therefore is to implement this in your Power Query Calendar function @sam.mckay has already provided an excellent basic calendar, you can download that from the resources here.

[satichun]

Thank you @Melissa. Got you. Makes total sense. Portability does indeed save a lot of time for future work.

One mismatch I am seeing in your attached file is for 1st of March - looks like the offset is not working for this specific date. I was thinking to add an ‘OR’ condition in the month offset field and include an offset of -1. But that isnot going to work, is it?
VAR LMifBlank =
CALCULATE( MAX( Dates[Date] ),
FILTER( ALL( Dates ), OR(Dates[MonthOffset] = -2, Dates[MonthOffset] = -1 ))
)