Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi,
A dates-challenge here. I have an invoice table like the simple one at the bottom of this post. I added a slicer from a date in a calendar table to look at a certain point in time in this history. So the slicer filters the invoices.
But I also want the date value in the slicer to be a reference value for a calculation of open invoices.
For example: when i look at reference date 22-1 only 200 is unpaid, and it's in a category less than 1 month.
reference date | 22-1-2020 | |
open < 1 month | 200 | from invoice I1 |
open > 1 month | 0 |
|
But when I take a reference date 2-4, there's 1000 open from recent invoices in the past month and 200 from earlier invoices.
reference date | 2-4-2020 | |
open < 1 month | 1000 | from invoice I4 |
open > 1 month | 200 | from invoice I2 |
And finally when i set the reference date to 2-7, all is well
reference date | 2-7-2020 |
open < 1 month | 0 |
open > 1 month | 0 |
I managed to make the calculated columns when I used the NOW() function as a reference date. But when I try to use the reference date from a slicer in a measure like max(calendar.date) the calculated columns don't return the results I expected. I read something about not using measures in calculated columns. But I'm stuck now. Anybody wants to help me on the calculated columns and measures needed for this?
Here's the table:
Date | Invoice | Invoiced | Paid |
1-1-2020 | I1 | 1000 | 0 |
2-1-2020 | I1 | 0 | 800 |
1-2-2020 | I2 | 1000 | 0 |
3-2-2020 | I2 | 0 | 800 |
1-3-2020 | I3 | 1000 | 0 |
3-3-2020 | I3 | 0 | 1000 |
3-3-2020 | I2 | 0 | 200 |
1-4-2020 | I4 | 1000 | 0 |
5-4-2020 | I4 | 0 | 1000 |
5-4-2020 | I1 | 0 | 200 |
1-5-2020 | I5 | 1000 | 0 |
1-6-2020 | I6 | 1000 | 0 |
5-6-2020 | I6 | 0 | 1000 |
Solved! Go to Solution.
Thanks Resident Rockstar. Santa does exist !!! 😁
Also appreciate you deliver the answer in a way that I can learn from it.
Hi, @RudyL , you might want to try the pattern in the attached file.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Thanks Resident Rockstar. Santa does exist !!! 😁
Also appreciate you deliver the answer in a way that I can learn from it.
@RudyL , You have try like this for date slicer from Date/Calendar Table
refer example
measure =
var _max = maxx(allselected('Date'), 'Date'[Date])
return
averageX(values(Table[invoice]), datediff(max(table[Date]),_max,day))
Thanks for this part of the solution. Very helpful.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
13 | |
12 | |
11 | |
9 |
User | Count |
---|---|
65 | |
20 | |
20 | |
17 | |
11 |