Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a table that has receipt_date and closure_date and those are also broken into calendar years so I also have receipt_CY and closure_CY.
I created a measure for the current year - just YEAR(TODAY()) - called Current_CY.
I want to have a page showing "Current".
It should show all records where receipt_CY = current_CY or closure_CY = current_CY or where receipt_date is not null and closure_date is null.
I tried building a dax filter but I can't compare receipt_CY to current_CY because receipt_CY (or closure_CY) has different values (obviously).
So table is like:
Unique_seq | receipt_date | reciept_cy | closure_date | closure_cy |
1 | 1/1/2020 | 2020 | ||
2 | 3/1/2020 | 2020 | 1/1/2021 | 2021 |
3 | 1/1/2021 | 2021 | ||
4 | 4/1/2020 | 2020 | 6/1/2020 | 2020 |
In this example, the "current" page would include records 1,2, and 3.
I figure I need to build a measure that I can use to drop in the filter but I've not been successful at figuring it out.
I know this shouldn't be that complicated but I'm stuck.
Solved! Go to Solution.
Hi, @Rjesak
Please check the below picture and the sample pbix file's link down below.
Instead of having extra columns for a year in the fact table, try having a dim-calendar table like the below, and create a measure like below.
Count Unique Seq =
IF (
ISFILTERED ( 'Calendar' ),
CALCULATE (
COUNTROWS ( data ),
FILTER (
data,
AND (
data[receipt_date] <= MAX ( 'Calendar'[Date] ),
OR (
data[closure_date] >= MIN ( 'Calendar'[Date] ),
data[closure_date] = BLANK ()
)
)
)
)
)
https://www.dropbox.com/s/d26henlpvp41c87/rjesakv2.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: https://www.linkedin.com/in/jihwankim1975/
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Sorry everyone, I'm killing myself on a project so I'm working through these one by one to determine which is going to be best. I apologize for the delay.
Hi @Rjesak ,
Does your problem have been solved? If it is solved, please mark a reply which is helpful to you.
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
Hi @Rjesak ,
Try the following formula:
Measure =
var tab =
CALCULATETABLE(
VALUES('Table'[Unique_seq]),
FILTER(
'Table',
'Table'[reciept_cy] = [Current_CY]
|| 'Table'[closure_cy] = [Current_CY]
|| ( NOT(ISBLANK('Table'[receipt_date]))
&& NOT(ISBLANK('Table'[closure_date])) )
)
)
return
COUNTROWS(
INTERSECT(
tab,
VALUES('Table'[Unique_seq])
)
)
Then filter the Unique_seq field in the visuals:
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Rjesak
Please check the below picture and the sample pbix file's link down below.
Instead of having extra columns for a year in the fact table, try having a dim-calendar table like the below, and create a measure like below.
Count Unique Seq =
IF (
ISFILTERED ( 'Calendar' ),
CALCULATE (
COUNTROWS ( data ),
FILTER (
data,
AND (
data[receipt_date] <= MAX ( 'Calendar'[Date] ),
OR (
data[closure_date] >= MIN ( 'Calendar'[Date] ),
data[closure_date] = BLANK ()
)
)
)
)
)
https://www.dropbox.com/s/d26henlpvp41c87/rjesakv2.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: https://www.linkedin.com/in/jihwankim1975/
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@Rjesak , Not very clear. You need to have a common date table joined to both dates one join will be inactive.
Assume receipt date join is active
Create measures like
CALCULATE(Count(Table[Unique_seq]),DATESMTD('Date'[Date]))
calculate( calculate( Count(Table[Unique_seq]),USERELATIONSHIP ('Table'2[Close Date], 'Date'[Date])),DATESMTD('Date'[Date]))
refer
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
81 | |
70 | |
62 |
User | Count |
---|---|
148 | |
116 | |
104 | |
90 | |
65 |