Forum Discussion
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:
I tried with the following measure but it returns blank:
17 Replies
- 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.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.
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))Measure
Result
If your problem is solved then please accept this as solution.
Thank you
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 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/434008Thank 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.
Hi Anonymous ,
You could refer to my sample for details. If this is not what you want, please correct me.
Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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,
