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.
Hello,
I am attempting to sum balances of invoices in a relative date range in the past. However, it returns some results outside of the date window. As of today (10/18/20) it should ONLY return dates between 8/20/20 through 9/19/20. Most items are in that range... however, a handful of results on the table are outside of that range... and I'm about to pull my hair out lol.
Any help would be awesome!
30-59 OVERDUE Balances = CALCULATE (
SUM('ACCOUNTS_PAYABLE'[INVOICE_AMOUNT]),
ACCOUNTS_PAYABLE[Unpaid?] in {"Unpaid"},
DATESBETWEEN( ACCOUNTS_PAYABLE[INVOICE_DUE_DATE], TODAY()-59, TODAY()-30)
)
Solved! Go to Solution.
Hi,
You should have a Calendar Table with a relationship to the invoice date column. To your visual, drag Date from the Calendar Table. Write this measure
30-59 OVERDUE Balances = CALCULATE(SUM('ACCOUNTS_PAYABLE'[INVOICE_AMOUNT]),ACCOUNTS_PAYABLE[Unpaid?] in {"Unpaid"},DATESBETWEEN(Calendar[DATE], TODAY()-59, TODAY()-30)
)
Hope this helps.
Hi,
You should have a Calendar Table with a relationship to the invoice date column. To your visual, drag Date from the Calendar Table. Write this measure
30-59 OVERDUE Balances = CALCULATE(SUM('ACCOUNTS_PAYABLE'[INVOICE_AMOUNT]),ACCOUNTS_PAYABLE[Unpaid?] in {"Unpaid"},DATESBETWEEN(Calendar[DATE], TODAY()-59, TODAY()-30)
)
Hope this helps.
You are welcome.
@cprine , Seem fine.
Try like
30-59 OVERDUE Balances =
var _min = TODAY()-59
var _max - TODAY()-30
return
CALCULATE (
SUM('ACCOUNTS_PAYABLE'[INVOICE_AMOUNT]),
filter(ACCOUNTS_PAYABLE, ACCOUNTS_PAYABLE[Unpaid?] in {"Unpaid"} && ACCOUNTS_PAYABLE[INVOICE_DUE_DATE] >= _min && ACCOUNTS_PAYABLE[INVOICE_DUE_DATE] <=_max)
)
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.
super
so that a 0 comes out in case there is no data I have completed it with
Hi,
You may try this measure
=coalesce([Abiertos365],0)
Hope this helps.
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 |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |