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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Poo1405
Frequent Visitor

how to compare multiple value in 2 different period of time

1. I need to create a measure as a filter that can choose the period time between columns "date start" and "date end" of table "date dim of lead (2 independent filter orders to choose 2 different time periods)
2. I need to compare the results from the filters.

Poo1405_0-1627167907634.png 

Poo1405_1-1627167938981.png

I provide 2 pictures, one is the model relationship, and one is my concept to filter and compare the results that the 2 filters pointed out.

I've searched and followed similar topics before (time intelligence), but I haven't been able to do it.

Please support me and request me if you have any questions about my description of my problem.

 

1 ACCEPTED SOLUTION
PaulDBrown
Community Champion
Community Champion

I would venture that you need to duplicate the date table to use in the comparison measure. Link the duplicate date table to the fact table in an inactive relationship and use the function USERELATIONSHIP in the comparison measure and TREATAS to link to the date values in the visuals





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

9 REPLIES 9
Poo1405
Frequent Visitor

Hi @PaulDBrown
The algorithm has a problem when I try to choose the start time towards the middle of the date comp slicer. The total doesn't seem to work very well even though it shows full details of the total amount per week but is not counted.

Can you check it again for me? Here is a picture I took and red-circled some of my doubts.

Poo1405_0-1627559421445.png

 

@Poo1405 

 

To get the correct totals, use the following measure:

Comp Date lead total =
SUMX ( VALUES ( 'Axis Table'[WeekNum] ), [Comp date lead] )

 

sumx.PNG

 

I've attached the edited file





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Poo1405
Frequent Visitor

Yep, thank you so much! 
It worked very well. I can rely on your file to do the same with other metrics in my dashboard,
but I have a question, is the USERELATIONSHIP function needed in my dashboard? Do you have any idea for me?

No, you don't need the USERELATIONSHIP function for the calculation you are looking for. You would use it to activate the relationship between the date and the end date, but as it is, the start/end dates are periods referring to weeks, so the end date is redundant.





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Your advice is super useful for me. Thank you very much! I very appreciate it! @PaulDBrown 

Poo1405
Frequent Visitor

Poo1405_0-1627283561274.png

So, Let me clarify once again my intentions on the sample data sheet above. I want to create a dashboard that can track and analyze over time my phase of lead and cost-related metrics.
My concept is to create a time filter between the start time and end time; one more is a function that can duplicate the newly created filter to compare columns on the same value but different intervals (intervals I can choose based on filter).

I have consulted many topics on the forum, but none are really similar to my case. It would be greatly appreciated to easily get specific instructions to get used to DAX on the Power BI.

@Poo1405 

 

See if this works for you:

Time selection.gif

 

I've attached the sample file for you:

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






PaulDBrown
Community Champion
Community Champion

I would venture that you need to duplicate the date table to use in the comparison measure. Link the duplicate date table to the fact table in an inactive relationship and use the function USERELATIONSHIP in the comparison measure and TREATAS to link to the date values in the visuals





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.







May I give you some data examples, you could help me with the solution in particular. I'm stuck in the middle of not knowing which implementation (time comparison, comparing 2 objecting, filter between date start and date end columns) to implement to achieve my intention. 
https://drive.google.com/file/d/1xXFu7JsyU2xHuiDW0ARRSdIb0TULXJWV/view?usp=sharing 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.