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 everyone,
I currently have a table with dates for invoices sent and invoices received.
I want to calculate how many invoices weren't paid for over 30 days in the past (I have the data available for this).
Essentially I want my output to be a line graph with the count of invoices that weren't paid within 30 days within each historic month. Is there a measure anyone can think of that would be able to do this?
Thanks,
Tom
Solved! Go to Solution.
Hi @Tomhayw ,
The project 3 should not be included due to the invoice paid on Feb 07, 2022... Am I right? I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create a measure as below:
Measure =
VAR _year =
SELECTEDVALUE ( 'Date'[Date].[Year] )
VAR _month =
SELECTEDVALUE ( 'Date'[Date].[MonthNo] )
VAR _seledate =
EOMONTH ( DATE ( _year, _month, 1 ), 0 )
VAR _count =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Project name] ),
FILTER (
'Table',
'Table'[Invoice sent] < _seledate
&& 'Table'[Invoice paid] > _seledate
&& DATEDIFF ( 'Table'[Invoice sent], _seledate, DAY ) > 30
)
)
RETURN
_count
Best Regards
Hi @Tomhayw ,
Do you want to get a line chart and display the data with the count of the invoices which weren't paid within 30days? In order to get a better understanding on your requirement and give you a suitable solution, could you please provide some raw data in your invoice table (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
Hi there,
I've attached a screenshot of what the raw data looks like, and what an expected output would look like:
Data:
In these months, I want to see how many invoices were still outstanding which had >30 days with no invoice paid as of the end of each month
Desired output:
I hope this provides some clarity on my problem
Hi @Tomhayw ,
Thanks for your reply. What's the calculation logic? Why it is 1 in Jan, 3 in Feb, 2 in March? Could you please provide the special examples to explain it base on your sample data. Thank you.
Best Regards
Essentially what I mean is that if I was to look in Feb-22, how many of those projects had invoices that still weren't paid at the end of the month and where the number of days since invoice sent > 30.
So for Feb, project 1, 2 and 3 still hadn't been paid by the end of February, and the number of days since the invoice sent to the end of February >30, so you would count those three.
Hi @Tomhayw ,
The project 3 should not be included due to the invoice paid on Feb 07, 2022... Am I right? I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create a measure as below:
Measure =
VAR _year =
SELECTEDVALUE ( 'Date'[Date].[Year] )
VAR _month =
SELECTEDVALUE ( 'Date'[Date].[MonthNo] )
VAR _seledate =
EOMONTH ( DATE ( _year, _month, 1 ), 0 )
VAR _count =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Project name] ),
FILTER (
'Table',
'Table'[Invoice sent] < _seledate
&& 'Table'[Invoice paid] > _seledate
&& DATEDIFF ( 'Table'[Invoice sent], _seledate, DAY ) > 30
)
)
RETURN
_count
Best Regards
Yes you are correct re project 3 not being included. I've tried to replicate this measure in my PBI file but again the incorrect values are being returned.
My date dimension table and invoice received date have a relationship between them. Is this potentially why?
EDIT:
Here is what my actual table looks like (sensitive data redacted):
And this is the relationship with date dimension table:
Note that the relationship joins invoice received date to the date dimension.
I hope this provides greater clarity
Hi @Tomhayw ,
Please remove the relationship between your fact table and Datedim table, and check if you can get the correct result. If no, please provide more raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file without sensitive info. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
Hi thanks,
This seems to have worked but I really need a relationship between the date and fact table as I am undertaking other analysis on this dataset. Is there a way I can incorporate both?
Thanks.
Hi @Tomhayw ,
You can consider to create another date dimension table and don't create any relationship with your fact table. And apply the date field onto your visual to replace the original date field...
If it still not working, could you please share a simplified pbix file with me? You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
@Tomhayw , Last 30 without a date selection
example measure =
var _max = maxx(allselected(date),date[date]) // or today()
var _min = _max -30
return
CALCULATE(SUM(Sales[Sales Amount]),filter(date, date[date] <=_max && date[date] >=_min))
but you want select a date an then want last 30 , then you need slicer on an independent date table
//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = _max -30
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))
Hi,
I tried your measure based on your guidance:
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |