Forum Discussion

Novice_YVR's avatar
Novice_YVR
Regular Visitor
2 years ago

Calculating measure for non-existent data

Hi all,    I'm working with Power Pivot but since it's DAX, it should be the same as working with Power BI I hope.  My problem deals with missing data in my dataset.  Let's say I have 2 month's w...
Need Help
Ashish_Mathur's avatar
Ashish_Mathur
Super User
2 years ago

Hi,

Do these measures work?

Measure1 = SUMX(FILTER(VALUES(Data[Account]),[MOM change]<0),[MOM change)

Measure2 = SUMX(FILTER(VALUES(Data[Account]),[MOM change]>0),[MOM change)

Novice_YVR's avatar
Novice_YVR
Regular Visitor
2 years ago

These measures do not work.

I think the key issue is there's no data existing for account B in Jan. 

Is there a way to insert a dummy record with the balance at 0 to compensate for this? 

 

Thanks. 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Hi,

    Share the dowload link of the PBI file and show the expected result very clearly.

    • Novice_YVR's avatar
      Novice_YVR
      Regular Visitor
      2 years ago

      Hi, 

       

      Apologies as I am unable to share a PBIX file. 

      I'm hoping the screenshot will suffice. 

      Table 1 would be the raw data that I have; using the measures in my original post, table 2 is generated. The results as presented in table 3 is what I am expecting/seeking (This is a hardcoded table that I inserted). 

      As you can see, Account B is missing in Table 2 which I suspect is because there is no record for it in Feb as per table 1. 

       

      Thank you. 

       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        2 years ago

        Hi,

        I cannot understand anything from the screenshots.