Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
first I'm new here, I didn't find an opportunity to attach a pbix file, how is this possible?
My problem is the following: I have a data table, a simplified version looks like this.
Now I would like to have two filters in Power BI, I thought about two disconnected tables. They should allow to set a "Start Date" and an "End Date". Then I want to see, how the Customers have developed between Start and End date. The tricky part is, that I only want to compare the customers, that are available in the selected Start Date. The next screenshot shows a wrong result, but basically this is how I want to display it in Power BI:
The forumla for "End" is currently:
End =
VAR EndDate = MIN('End Date'[Date])
Return CALCULATE(DISTINCTCOUNT(Data[Customer]),FILTER(Data,Data[Date]=EndDate))
Now I would like to have a formula, which takes only the 2 customers selected for the Start (this is customer nr 1 and 2), and shows what the current Priority for these two customers is at the "End" Date. The right result would be (as customer 2 was Priority 4 in Jan 2000 and moved to Priority 1 in Jun 2000):
Would be really great to get some help here, I am already despairing and trying to solve the problem for a long time
Solved! Go to Solution.
Hi @Landon1 ,
I suggest you to try this code to create a measure.
End =
VAR StartDate =
MIN ( 'Start Date'[Date] )
VAR EndDate =
MIN ( 'End Date'[Date] )
VAR LIST =
CALCULATETABLE (
VALUES ( Data[Customer] ),
FILTER ( ALL ( Data ), Data[Date] = StartDate )
)
RETURN
CALCULATE (
DISTINCTCOUNT ( Data[Customer] ),
FILTER ( Data, Data[Date] = EndDate && Data[Customer] IN LIST )
)
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Landon1 ,
I suggest you to try this code to create a measure.
End =
VAR StartDate =
MIN ( 'Start Date'[Date] )
VAR EndDate =
MIN ( 'End Date'[Date] )
VAR LIST =
CALCULATETABLE (
VALUES ( Data[Customer] ),
FILTER ( ALL ( Data ), Data[Date] = StartDate )
)
RETURN
CALCULATE (
DISTINCTCOUNT ( Data[Customer] ),
FILTER ( Data, Data[Date] = EndDate && Data[Customer] IN LIST )
)
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.