Forum Discussion

Icon for Helper IV rank

Helper 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: ...

amitchandak
5 years ago
jvandyck , if want how to use these two tables you can refer this

https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

On-trend , I am not clear, to have a trend you need to use a common table/column .

for that; Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Icon for Super User rank

Super User

5 years ago

jvandyck , if want how to use these two tables you can refer this

https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

On-trend , I am not clear, to have a trend you need to use a common table/column .

for that; Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

jvandyck
Helper 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
  Helper IV
  5 years ago
  forgot to paste the link: https://acerta-my.sharepoint.com/:u:/g/personal/joos_van_dyck_acerta_be/EfVVyRyKMSNCmzuroX66XlMBCWoYH6ofIp7BZe8xnrqLEw?e=uJ3IWn
jvandyck
Helper 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