Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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. 

 

 

AllisonKennedy's avatar
AllisonKennedy
Icon for Community Champion rankCommunity Champion
6 years ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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,