Different Statistics Based on Various Date Fields

[Anonymous]

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

[v-frfei-msft · Accepted solution]

Hi @Anonymous,

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

1. Create the relationship between tables like this.

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))

For more details, please check the pbix as attached.

https://www.dropbox.com/s/sjmcwwceldlwv6v/Different%20Statistics%20Based%20on%20Various%20Date%20Fields.pbix?dl=0

Regards,

Frank

[Anonymous]

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]))

[Greg_Deckler]

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.