Last year comparative same day (monday vs monday, 01 vs 01))

I would like to know the best way to calculate the following periods versus last year:

  1. daily sales (last sale date) vs daily sales LY
  2. Sales MTD , vs MTD LY -month to date
  3. Sales QTD vs, sales QTD LY --quarter to date
  4. Sales YTD vs, sales YTD LY --year to date
  5. Sales YTM vs sales YTM LY – year to month

based on 2 approaches for comparison (if possible in the same model)
a) same day number - i.e. 01 Jan 2020 vs/ 01 Jan 2019
b) same day of week - Monday vs Monday, etc,

Thanks in advance for your help
Best
Javier

Hi @JavierCD, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.

  • When posting a topic with formula make sure that it is correctly formatted to preformated text </>.

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Hi @JavierCD, due to the length by which this post has been active without response, we are tagging it as Solved. For further questions related to this post, please make a new thread for better visibility. Thanks!

just to learn how to get and answer to this topic. is anything wrong about the question? any advice?
Thanks

Hi @JavierCD,

I believe you did not get desired attention as you did not include sample file. I have created measures for you and these are quite straight forward in DAX. Please find attached and your measures are present in “Time Intelligence” section. Please note that in order for time intelligence to work in Power BI, you need to have following things:

1) Date table with no missing dates for particular year
2) Mark date table as date
3) Specify date column and ensure proper relationship :slight_smile:

Please feel free to get in touch in case of any concerns.

Cheers!

EDNA_Time Intelligence.pbix (3.6 MB)

1 Like

Thanks a lot Hafizultan for your answer. Interesting calculation of ytm

i have similar calculations for comparing different periods versus last year. (same day number): 1st Jan. 2020 vs 1st Jan. 2019

i still don´t know the best way to compare with the same day of the week versus last year (Monday vs Monday). Do you have any idea for those calculations: last sale day, mtd, qtd, ytd

Best Regards
Javier

Hi @JavierCD,

That is an interesting scenario and I think it will depend how you want to utilize that function and you need to be careful of filter context. I have created a sample measure and it is working for me.

Below you will see that all number of particular day of week are matching.

image

DAX:

Total Sales_LY_Sameday =
CALCULATE (
    [Total Sales],
    FILTER ( ALLSELECTED ( dDate ), 'dDate'[Year] = MAX ( 'dDate'[Year] ) - 1 ),
    FILTER (
        ALLSELECTED ( dDate ),
        'dDate'[Week Number] = MAX ( 'dDate'[Week Number] )
    ),
    VALUES ( 'dDate'[DayOfWeekName] )
)

EDNA_Time Intelligence.pbix (3.6 MB)

1 Like

Thanks a lot hafizsultan!
Great pattern!
I will try in my model
Best regards
Javier