Forum Discussion

jvandyck's avatar
jvandyck
Icon for Helper IV rankHelper IV
5 years ago
Solved

calculate measure between dates

Hi

 

I am a bit stuck here. I have a power bi report with 2 date tables (date from and date to). These have an inactive relation to my fact table. This allows me to easily calculate the variance: 

Value from = CALCULATE(SUM('Key Figures'[Value]), USERELATIONSHIP('Key Figures'[Referentie maand],'Date from'[Date]))
Value to = CALCULATE(SUM('Key Figures'[Value]), USERELATIONSHIP('Key Figures'[Referentie maand], 'Date to'[Date]))
Variance = [Value to]-[Value from]
 
Now the next step is to build a trend showing the dates between "date from" and "date to", and this is where I am stuck. Here too, I would like to pass the relationship but then with a datesbetween or something like that, but this does not work. Can somebody help me out?
 
THANKS!
Need Help

5 Replies

    • jvandyck's avatar
      jvandyck
      Icon for Helper IV rankHelper IV
      5 years ago

      Hi

      Thanks for your help. On this link, you should be able to take a look at a mock-up I prepared. It contains the PBIX file as well as some fake data. You will see how I calculated the Variance. The issue is the trend chart...I want it to be controlled by the date from and to listbars....

       

      Thanks again

      Joos

    • jvandyck's avatar
      jvandyck
      Icon for Helper IV rankHelper IV
      5 years ago

      Your post finally helped me out! I removed the inactive relationships:

      And I played around with calculated measures:

      Value from = (
      var curmonth=min('Date from'[Date])
      return
      CALCULATE(SUM('Key Figures'[Value]), 'Key Figures'[Referentie maand]=curmonth)
      )
       
      Value to = (
      var curmonth=min('Date to'[Date])
      return
      CALCULATE(SUM('Key Figures'[Value]), 'Key Figures'[Referentie maand]=curmonth)
      )
       
      Variance = [Value to]-[Value from]
       
      For the trend chart, I created a measure like this:
      Value between = (
      var datefrom=min('Date from'[Date])
      var dateto=min('Date to'[Date])
      return
      calculate(sum('Key Figures'[Value]),filter(all('Key Figures'[Referentie maand]),'Key Figures'[Referentie maand]>=datefrom &&'Key Figures'[Referentie maand]<=dateto
      )))
       
      My trend chart has this last measure as value and referentiemaand on the axis. Only the months between "date from" and "date to" are shown.
       
      Thanks a lot for your help