Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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:
I'm not sure why it doesn't work for single days
Thanks,
@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
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.
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,
Hey @Anonymous ,
Your problem is solved please refer the below measure written in the screenshot.
Last year units Produced =
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:
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
Hi @Anonymous
Share the sample source file and the .pbix file
Thank You
Hi @Ajinkya369 ,
Please find below the link for the source file:
https://drive.google.com/file/d/1KmlWhV5rjrjlm5LxivV_H04FI2VcH4lY/view?usp=sharing
Thank you,
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,
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
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:
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.
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
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
Thank you,
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |