Forum Discussion

qwerttty's avatar
qwerttty
Helper I
3 years ago
Solved

Find the difference between two dates value

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 i...
Need Help
Show and Tell
Tutorial Requests
  • johnyip's avatar
    johnyip
    3 years ago

    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's avatar
qwerttty
Helper I
3 years ago

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's avatar
johnyip
Solution Sage
3 years ago

qwerttty Can you show the relationships of the tables?

  • qwerttty's avatar
    qwerttty
    Helper I
    3 years ago

    Hi johnyip ,

    Here are the relationship table.

    Thanks for your help. 

    Kind regards. 

     

    • johnyip's avatar
      johnyip
      Solution Sage
      3 years ago

      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's avatar
        qwerttty
        Helper I
        3 years ago

        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.