Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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 transact...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    8 years ago

    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

v-frfei-msft's avatar
v-frfei-msft
Icon for Community Support rankCommunity Support
8 years ago

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's avatar
    Anonymous
    Not applicable
    8 years ago

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