Forum Discussion

OscarIvanCampos's avatar
OscarIvanCampos
Icon for Helper I rankHelper I
6 years ago

Invoicing Balance using DAX

Hi DAX experts, 

 

I am having a hard time to build a measure that I thought was simple...

 

Here is the scenario:

We got a SQL transactional table with all the invoices from 2015. In this table, we also have the payment date and service rendered date.

There is a calendar table in Power BI with one active relationship link to the service date (More measures are based on this). We also have two more inactive relationships link to the payment date and invoice date.

What we need is to develop a measure that calculates the balance of all the invoices opened based on the EOM (Calendar Table) and use a matrix table that looks like the one below. 


Example

 

Any thoughts

 

Thanks in advance. 

 

Oscar

1 Reply

  • Stachu's avatar
    Stachu
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Can you add sample tables (in format that can be copied to PowerBI) from your model with anonymised data? Like this (just copy and paste into the post window).

    Column1 Column2
    A 1
    B 2.5

    in general you need USERRELATIONSHSIP to activate relationship (I assume you need to sum per Invoice date, correct?)

    https://docs.microsoft.com/en-us/dax/userelationship-function-dax

    Also I would assume that a single service rendered date has more than one invoice/payment date, is that the case? how do show it in the visual?