Forum Discussion

DevonVanDam's avatar
DevonVanDam
Icon for Helper I rankHelper I
3 years ago
Solved

Calculate DSO (Days Sales Outstanding) dynamically

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 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

4 Replies

  • DevonVanDam's avatar
    DevonVanDam
    Icon for Helper I rankHelper I
    3 years ago

    The flex solution is:

    DSO Measure =
    SUMX(
        DSO,
        DSO[DSO] * DIVIDE(DSO[Invoice Value],
                              CALCULATE(SUM(DSO[Invoice Value]),
                                        ALLSELECTED(DSO)))
    )
  • DevonVanDam's avatar
    DevonVanDam
    Icon for Helper I rankHelper I
    3 years ago

    Is the data correctly shared via WeTransfer (or doesn't work? - not allowed?)

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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

    • DevonVanDam's avatar
      DevonVanDam
      Icon for Helper I rankHelper I
      3 years ago

      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?