topic DAX for Calculating Product status Reporting Period (DD-MM) in DAX Commands and Tips

DAX for Calculating Product status Reporting Period (DD-MM)

Anonymous — Sat, 01 Aug 2020 21:05:29 GMT

Hi Team,

Can you suggest with DAX calculation on How to calculate Product status based on Reporting period and which method would be the best to start.

Below are the logic details how want to calculate a Product details -

If a Product_ID is less than 6 months old prior to the current reporting period, they are not required to submit, so should only display as 'Not required'
If a Product_ID is over 6 months prior to current reporting period we want to display their information as 'Not Complete' if a record is not listed as Closed
If a Product_ID ends before the current reporting period we do not want to display them because they are no longer active, so show product as 'Not Required'

Reporting Period Details are-

	Reporting Period	Quarter
Jan Month Start -Mar Month End	01-01 to 31-03	Q1
April Month Start -June Month End	01-04 to 31-05	Q2
July Month Start -Sep Month End	01-07 to 31-09	Q3
Oct Month Start -Dec Month End	01-10 to 31-12	Q4

I'm attaching sample data in PBIX file.

@ Product_Status.pbix

Re: DAX for Calculating Product status Reporting Period (DD-MM)

lbendlin — Sat, 01 Aug 2020 22:40:19 GMT

Your period table has an error, should say June 30, not May 31, and September 30, not September 31.

Your sample data only has one product ID.

On the first row the Start date is after the end date?

Note that if you include dates that are way in the past (second row) you are artificially inflating the storage memory requirements

Which of the dates should be used for the calculation? The end date or the completion date?

Any particular reason for not using a Calendar/Dates table?

Re: DAX for Calculating Product status Reporting Period (DD-MM)

Anonymous — Sun, 02 Aug 2020 06:28:01 GMT

Hey lbendlin ,

Thanks for the reply

Here is the reporting period re-vamp details -

	Reporting Period	Quarter
Jan Month Start -Mar Month End	01-01 to 31-03	Q1
April Month Start -June Month End	01-04 to 30-05	Q2
July Month Start -Sep Month End	01-07 to 30-09	Q3
Oct Month Start -Dec Month End	01-10 to 31-12	Q4

Any particular reason for not using a Calendar/Dates table?

To answer to your question, We dont have Calender tables based on reporting period, If you could share this problem with DAX using Calender table and this approach is best then its fine.

Here is the update data with PBIX file -

@ Product_Status.pbix

Thanks

Re: DAX for Calculating Product status Reporting Period (DD-MM)

Anonymous — Mon, 03 Aug 2020 18:40:44 GMT

Hi All,

I will trying to calculate this, can anyone suggest best method Please