cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Dax Measure - Same Time Last Year - multiple date columns

Hello,

 

I have a table which has the following columns:

 

Production Date, As Of Date, Units produced

 

In my report, I have a dropdown filter to select the most recent As of date, and another one to select the next 90 days of production date, starting from 8/1/2020, for example.

 

I am trying to get a measure of Units produced last year (As of date is 7/9/2019) as follows: 

 

Units.PNG

 

I tried with the following measure but it returns blank:

 

Units Produced Last Year = CALCULATE(SUM(Table[Units Produced]),DATEADD(Table[As of date],-1,YEAR))
 
Any ideas?
 
Thank you

 

17 REPLIES 17
dax
Community Support
Community Support

Hi @Anonymous , 

You could refer to my sample for details. If this is not what you want, please correct me.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Anonymous
Not applicable

Hi @dax 

 

Thank you for providing a file. Unfortunately it didn't work.

 

Unfortunately that didn't worked. I think the reason is how my data is structured.

 

In my data, for all 2020 dates in "As of date" there is only production dates in 2020. Since there are not 2019 production dates for a single "As of date" in 2020, I think that is why it returns blank.

 

However, I would like to return what was the actual quantities produced in the same date last year.

 

Interesting, the following formula works if I have the  production Month name calculated (January, February..) instead of single date as required:

 

Produced 2019 = CALCULATE(sum(Table[Units Produced]),Table[Production date]>=date(2019,1,1),Table[Production date]<=date(2019,12,31),SAMEPERIODLASTYEAR(Table[As of date]))

 

I'm not sure why it doesn't work for single days

 

Thanks,

Greg_Deckler
Super User
Super User

@Anonymous You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Thank you @Greg_Deckler .

 

I already tried with DATEADD but I'm not getting any result.

 

Do you have a DAX code type TITHW for same time last year?

 

 

 

@Anonymous - Maybe, I have a To **bleep** with DATEADD article. https://community.powerbi.com/t5/Quick-Measures-Gallery/To-bleep-With-DATEADD/td-p/1259467

 

I'm going to have to revisit this thread though as I need to come back up to speed with what you are trying to accomplish and download your pbix/data.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Hi @Greg_Deckler !

 

Do you have an update about a possible STLY article?

 

I haven't found a solution for my issue yet.

 

Thank you,

Ajinkya369
Resolver III
Resolver III

Hey @Anonymous ,

 

Your problem is solved please refer the below measure written in the screenshot.

 

Last year units Produced =

CALCULATE(Sum('Table'[Units produced]),DATEADD('Table'[As of date].[Date],-12,month))

MeasureMeasure

 

ResultResult

If your problem is solved then please accept this as solution.

 

Thank you

 

 

Anonymous
Not applicable

Hello @Ajinkya369 

 

Unfortunately that didn't worked. I think the reason is how my data is structured.

 

In my data, for all 2020 dates in "As of date" there is only production dates in 2020. Since there are not 2019 production dates for a single "As of date" in 2020, I think that is why it returns blank.

 

However, I would like to return what was the actual quantities produced in the same date last year.

 

Interesting, the following formula works if I have the  production Month name calculated (January, February..) instead of single date as required:

 

Produced 2019 = CALCULATE(sum(Table[Units Produced]),Table[Production date]>=date(2019,1,1),Table[Production date]<=date(2019,12,31),SAMEPERIODLASTYEAR(Table[As of date]))

 

I'm not sure why it doesn't work for single days

 

Thanks,

 

 

Hey @Anonymous ,

 

Please try DATEADD function instead of SAMEPERIODLASTYEAR function.

 DATEADD(DateTime[DateKey],-365,day)  

Check with this above dax query.

If your problem is solved then please accept this as solution.

Please feel free to contact me for more help .

 

Thank You

Anonymous
Not applicable

Hi @Ajinkya369 

 

DATEADD doesn't work either. 

 

Thank you,

Hi @Anonymous 

Share the sample source file and the .pbix file

 

Thank You

Anonymous
Not applicable

Hi @Ajinkya369 ,

 

Please find below the link for the source file:

 

https://drive.google.com/file/d/1KmlWhV5rjrjlm5LxivV_H04FI2VcH4lY/view?usp=sharing

 

Thank you,

 

Anonymous
Not applicable

Hi @Ajinkya369 

 

Below is the link for the pbix file, including the 2019 measure which is not working.

 

https://drive.google.com/file/d/1a4XtBg-dVVtsd8Guu9l8pVWXJXCEWgBC/view?usp=sharing

 

Thanks,

AllisonKennedy
Super User
Super User

You need to use a date table for this.
https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html

I might also recommend using

DATEADD(DimDate[Date], -12, Month)
or even -365, DAY
depending on what result you are looking for.


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Anonymous
Not applicable

Hi @AllisonKennedy 

 

Thank you for the resource!

 

I created a DimDate table. So far I'm not getting results in the calculation, for the same date last year as suggested:

 

= CALCULATE(SUM(Table[Units Producted]),DATEADD(DimDate[Date],-365,DAY))

 

Since in my table I have 2 dates, Production Date and As of Date, should I create a relationship between DimDate and any of the 2 dates on my table?

 

I'm also using dropdown filters in the report for each one of the day types. 

 

 

@Anonymous 

Please see attached my update to the sample file you posted. I have used DAX to add a DimDate table, but still recommend doing this in the data model (ie Power Query). 

 

When working with date filters, if you have a filter for both date fields, this will filter your 'fact' table and therefore make it challenging to get your desired result.

 

I don't understand your specific data well enough to answer your question about which date to use for the relationship - can you explain your reporting requirements for As of Date vs Production Date? With Power BI we must pick only ONE field to create the active relationship, but you can create inactive relationships and use DAX USERELATIONSHIP() to access them if needed. 

 

The final rule when using time intelligence functions (like DATEADD) is to always use the DimDate field - in the matrix/table visual, in the slicer and in the DAX. You are trying to reconcile two date columns which is adding a bit to the confusion, so I have chosen to report and filter on Production date in the attached sample, but please let us know your specific requirements and definitions of as of date vs production date so we can assist further.


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Anonymous
Not applicable

Thank you @AllisonKennedy for having a look at my file

 

I created a DimDate table using M, and the measure for the last year works.

 

However, as you are aware, if I include a second visual dropdown filter for "As of date", there are no results in the Last Year column.

 

The idea is to show the the Units produced per day in a table, so I can add conditional formatting according to the units.

 

For example, the estimated units to be produced on a future date, which are part of the source data, will be different if compared today, and tomorrow (as of date).

 

Here is the link for the new file

 

DateAdd 2 

 

Thank you,

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.