Combine measures in 1 table filtered by date

[aer98]

Hello,

I want to combine 2 measures in a table and filter them by date.
You will find on the link below the file I am working on and as you can see, the second column (CA_2019_Cumulé) of the matrix displays the cumulative result on each line whereas it should correspond to the 1st column(CA_2018_Cumulé).

Click here to see the file

Thanks for your help,

[hthota]

The issue is due to the tables are not joined. It can be resloved by creating the Common Date table and join to that table or append the two table into single table and create the visual.

It would help you.

Thanks,

Heamanth Thota.

[aer98]

Thanks for your help.

In fact, one of the tables I use comes directly from an ERP and can't be modified.

So I have 1 table having more than 25000 lines and the other one has only 12, one for each month.

Is there any other option ?

[hthota]

[aer98]

Ok, thank you.

Would it be possible to have the PBIX file so that I can see exactly how it works ?

Thanks

[v-cherch-msft]

Hi @aer98 

You may use below measure.Attached sample file for your reference.

CA_2018_Cumulé =
CALCULATE (
    SUM ( 'CA-2018'[Montant] ),
    FILTER (
        ALL ( 'CA-2018' ),
        'CA-2018'[Date] <= MAX ( 'CA-2018'[Date] )
            && YEAR ( 'CA-2018'[Date] ) = 2018
    )
)

Regards,

[hthota · Accepted solution]

Create a table Date in Power Bi and Join them with other tables and build the visual.

Please see the attached file.

Thanks,

Hemanth Thota.