Find the difference between two dates value

[qwerttty]

Hi, 

I'm new in power bi. Wondering are there anyway to get the difference from the sum of month1 and sum of month 2. 

I use the measure: diff = [Sum of Month 2]-[Sum of Month 1]

I have edit the interactions of the filter, not sure is this the issue causing blank for the difference. 

Hope any can help me this issue. 

Thanks so much for the help. 

Kind regards. 

[qwerttty]

Hi @johnyip 

The dax are 

Sum of Month 1 =

CALCULATE(

   [total incoming],

    FILTER(

        'Water Flow Monthly',

        MONTH('Water Flow Monthly'[Timestamp])=MONTH(MAX('Calendar'[Date]))

        && YEAR('Water Flow Monthly'[Timestamp])=year(MAX('Calendar'[Date]))

    )

)

and 

Sum of Month 2 =

CALCULATE(

   [total incoming],

    FILTER(

        'Water Flow Monthly',

        MONTH('Water Flow Monthly'[Timestamp])=MONTH(MAX('Calendar (2)'[Date]))

        && YEAR('Water Flow Monthly'[Timestamp])=year(MAX('Calendar (2)'[Date]))

    )

)

I'm using two exact calendar table, because one calendar table cannot give me the different

[johnyip]

@qwerttty Can you show the relationships of the tables?

[qwerttty]

Hi @johnyip ,

Here are the relationship table.

Thanks for your help. 

Kind regards. 

[johnyip · Accepted solution]

By reading that, this should be caused by the bidirectional relationship of the two calender tables.

Although this may be be optimal (since I don't know if it is essential for you to keep the relationship between the calender table and water flow monthly)I, I think you can remove the relationship between that two calender tables, and then create another calender table. This should make you having 3 calender tables.

And then use that 2 standalone calender table in your slicers and DAX.

Please see if that works.

[qwerttty]

Hi @johnyip,

Thanks for your help after I create another calendar table. 

It works for me. 

Kind regards. 

[qwerttty]

Hi @johnyip ,

Thanks for your help. 

After standalone those calendar, I get a 0 for the difference, but it suppose be coming out value. Below are the relationship table

Thanks for your help. 

Kind regards. 

[Kishore_KVN]

Hello @qwerttty ,

To get difference between dates, use DATEDIFF DAX function and create your measure as per your need like to calculate DAYS/MONTHS/YEARS 

Dates Difference = DATEDIFF(Date1, Date2,Duration)

If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!

[qwerttty]

Hi @Kishore_KVN,

Thanks for the recommendation, 

however, I not need to get the different between two dates. 

I would like to get the amount from the selected date and find the difference of the amount between two selected date.

Thanks for your help. 

Kind regards. 

[johnyip]

What is the DAX of [Sum of Month 1] and [Sum of Month 2]?

[qwerttty]

Hi @johnyip, 

Thanks for your help. 

The dax are 

Sum of Month 1 =

CALCULATE(

   [total incoming],

    FILTER(

        'Water Flow Monthly',

        MONTH('Water Flow Monthly'[Timestamp])=MONTH(MAX('Calendar'[Date]))

        && YEAR('Water Flow Monthly'[Timestamp])=year(MAX('Calendar'[Date]))

    )

)

and 

Sum of Month 2 =

CALCULATE(

   [total incoming],

    FILTER(

        'Water Flow Monthly',

        MONTH('Water Flow Monthly'[Timestamp])=MONTH(MAX('Calendar (2)'[Date]))

        && YEAR('Water Flow Monthly'[Timestamp])=year(MAX('Calendar (2)'[Date]))

    )

)

I'm using two exact calendar table, because one calendar table cannot give me the different.

Kind regards.