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.
Hi all,
I am trying to create a dynamic DSO matrix table, but really struggeling. I have attached some dummy data which is in the same format. I also put in some results I am able to create in Excel (with slow formulas and allot of data) => but not in Power BI.
Should be able to create these tables based on the white data (not the grey formulas => used those to get the required answers)
Hopefully someone knows.
Thanks,
Devon
Data Data
Solved! Go to Solution.
Hi @DevonVanDam ,
I created a sample pbix file(see the attachment), please check if that is what you want.
DSO = DATEDIFF('Table'[Invoice date],'Table'[Payment date],DAY)
DSO Customer =
VAR _custinv =
CALCULATE (
SUM ( 'Table'[Invoice value] ),
FILTER ( 'Table', 'Table'[Customer] = EARLIER ( 'Table'[Customer] ) )
)
RETURN
DIVIDE ( 'Table'[DSO], _custinv ) * 'Table'[Invoice value]
DSO Area =
VAR _areainv =
CALCULATE (
SUM ( 'Table'[Invoice value] ),
FILTER ( 'Table', 'Table'[Sales area] = EARLIER ( 'Table'[Sales area] ) )
)
RETURN
DIVIDE ( 'Table'[DSO], _areainv ) * 'Table'[Invoice value]
DSO Company =
VAR _companyinv =
CALCULATE (
SUM ( 'Table'[Invoice value] ),
FILTER ( 'Table', 'Table'[Company] = EARLIER ( 'Table'[Company] ) )
)
RETURN
DIVIDE ( 'Table'[DSO], _companyinv ) * 'Table'[Invoice value]
Best Regards
The flex solution is:
Hi @DevonVanDam ,
I created a sample pbix file(see the attachment), please check if that is what you want.
DSO = DATEDIFF('Table'[Invoice date],'Table'[Payment date],DAY)
DSO Customer =
VAR _custinv =
CALCULATE (
SUM ( 'Table'[Invoice value] ),
FILTER ( 'Table', 'Table'[Customer] = EARLIER ( 'Table'[Customer] ) )
)
RETURN
DIVIDE ( 'Table'[DSO], _custinv ) * 'Table'[Invoice value]
DSO Area =
VAR _areainv =
CALCULATE (
SUM ( 'Table'[Invoice value] ),
FILTER ( 'Table', 'Table'[Sales area] = EARLIER ( 'Table'[Sales area] ) )
)
RETURN
DIVIDE ( 'Table'[DSO], _areainv ) * 'Table'[Invoice value]
DSO Company =
VAR _companyinv =
CALCULATE (
SUM ( 'Table'[Invoice value] ),
FILTER ( 'Table', 'Table'[Company] = EARLIER ( 'Table'[Company] ) )
)
RETURN
DIVIDE ( 'Table'[DSO], _companyinv ) * 'Table'[Invoice value]
Best Regards
Hi Yiruan,
Thank you very much for the swift reply. Is it possible to make this a measure? If I add in it in the columns its not dynamic when I selected a different period. Sorry my bad I did not mention this specifically, I assumed this was normal to think of => my bad!!
Do you know?
Is the data correctly shared via WeTransfer (or doesn't work? - not allowed?)
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 | |
105 | |
79 | |
68 | |
63 |
User | Count |
---|---|
148 | |
107 | |
106 | |
82 | |
70 |