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

Different Statistics Based on Various Date Fields

Hi,

I have a Date table and Trans table. They are connected Trans[TransDate] ->Date[Date]. I can successfully create various measures to generate stats based on the TransDate, such as total transaction quantity, total time spent etc. However, I also have Trans[ProdOrderCreateDate] and Trans[ProdOrderFinishDate]. I'd like to calculate how many production orders have been created and finished based on these two fields. The production order id has duplicates in the Trans table as it's denormilized and contains many transactions per production order. Could I possibly utilize measures to calculate it or should I change my table structure?

Sample structure:

  • Date
    • [Date]
  • Trans
    • [ProdOrderId] (duplicate values)
    • [TransDate] (related to Date[Date])
    • [ProdOrderCreateDate]
    • [ProdOrderFinishDate]

Thank you

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Different Statistics Based on Various Date Fields

Hi @5up,

 

I made one sample for your reference. You can refer to the following steps.

 

1. Create the relationship between tables like this.

2.PNG

 

2. Create the measures as below.

 

 

Createcount = CALCULATE(COUNTROWS(Trans),USERELATIONSHIP('Date'[Date],Trans[ProdOrderCreateDate]))
finishcount = CALCULATE(COUNTROWS(Trans),USERELATIONSHIP('Date'[Date],Trans[ProdOrderFinishDate]))
Transcount = CALCULATE(COUNTROWS(Trans))

 

 

 

Capture.PNG

 

For more details, please check the pbix as attached.

 

https://www.dropbox.com/s/sjmcwwceldlwv6v/Different%20Statistics%20Based%20on%20Various%20Date%20Fie...

 

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Super User
Super User

Re: Different Statistics Based on Various Date Fields

I am assuming that you want statistics on these things over something like per month, per day, per year or something like that, yes?

 

If so, you want to check out these two measures in the Quick Measure Gallery:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

 

You will need a disconnected date dimension.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Community Support Team
Community Support Team

Re: Different Statistics Based on Various Date Fields

Hi @5up,

 

I made one sample for your reference. You can refer to the following steps.

 

1. Create the relationship between tables like this.

2.PNG

 

2. Create the measures as below.

 

 

Createcount = CALCULATE(COUNTROWS(Trans),USERELATIONSHIP('Date'[Date],Trans[ProdOrderCreateDate]))
finishcount = CALCULATE(COUNTROWS(Trans),USERELATIONSHIP('Date'[Date],Trans[ProdOrderFinishDate]))
Transcount = CALCULATE(COUNTROWS(Trans))

 

 

 

Capture.PNG

 

For more details, please check the pbix as attached.

 

https://www.dropbox.com/s/sjmcwwceldlwv6v/Different%20Statistics%20Based%20on%20Various%20Date%20Fie...

 

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5up Frequent Visitor
Frequent Visitor

Re: Different Statistics Based on Various Date Fields

Hey @v-frfei-msft,

 

Thank you! That worked just fine, nice and easy. I did make a small tweak to your code to get it closer to my requirements. A quick test using a matrix and a visual work great.

 

Another question though, how can I create another table that will show only records from a selected cell that uses the new CreatedCount measure to display only relevant records. Below is a matrix screenshot based on the new measure. Usually, this kind of contectual filtering works fine with active relationships.

 

Also, can I create date filters (ex. CreatedDate filter) that use inactive relationship between the two tables?

 

 

CreatedCount = CALCULATE(DISTINCTCOUNT(Trans[ProdOrderId]),USERELATIONSHIP('Date'[Date],Trans[ProdOrderCreateDate]))

Pic.png