cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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

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 End01-01 to 31-03Q1
April Month Start -June Month End01-04 to 31-05 Q2
July Month Start -Sep Month End01-07 to 31-09Q3
Oct Month Start -Dec Month End01-10 to 31-12Q4

 

I'm attaching sample data in PBIX file. 

 

@ Product_Status.pbix

 

3 REPLIES 3
Highlighted
Community Champion
Community Champion

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

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

 

lbendlin_0-1596321194568.png

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?

 

Highlighted
Frequent Visitor

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

Hey @lbendlin ,

 

Thanks for the reply

 

Here is the reporting period re-vamp details -

 

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

 

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

 

Highlighted
Frequent Visitor

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

Hi All,

 

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

 

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.