Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jvandyck
Helper IV
Helper IV

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!
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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.

 

View solution in original post

5 REPLIES 5
jvandyck
Helper IV
Helper IV

In the meantime, i also tried this post: https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601.

It makes sense, but for some reason, I am not able to pass on the date selection to the variable in the calculated measure. Maybe someone can help me update the example PBIX I posted in my previous message?

amitchandak
Super User
Super User

@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.

 

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

jvandyck_0-1611321261427.png

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.