Forum Discussion

dtran's avatar
dtran
Icon for Helper II rankHelper II
8 years ago
Solved

Result in DAX not filtering correctly

I have a table with 2 months of data. 

Oct has all data but Nov has projected and real data.

Nov 1 to 13 is real data.

Nov 14 to 30 is projected data. 

I want to display in Power BI this sum(rev) for the current month.

 

In SQL Server the logic would be as follows:

select *
from my_table
where date_eom=(select eomonth(max(date_mtd)) from my_table)
order by 1

 

In Power BI Desktop, I have 2 measures created.

 

1) measure1 = EOMONTH( MAX(my_table[date_mtd]),0)

2) measure2 =  

CALCULATE(
sum(my_table[rev]),
FILTER(
my_table,
my_table[date_eom]=[measure1]
)
)

 

No problem with measure1 as it display the end of month date of Nov 30.

 

However, the problem is that the result shows all rev (YTD: Oct 1 to Nov 13) instead of only the current's month's rev (Nov 1 to Nov 30).

 

Thanks in advance!

 

 

 

Need Help
  • dtran's avatar
    dtran
    8 years ago

    Thanks v-ljerr-msft!

     

    I used your variable suggestion but I also need to use sumx and calculatetable to get the right data.

     

    I believe that if I just use the calculate and then use sum and filter within the calculate funtion, it would still filter out any projected values.

     

    Here's what ended up working for me:

    measure2 =
    VAR eom = [measure1] RETURN
    sumx(
    CALCULATETABLE(
    my_table,
    month(my_table[date_eom])=eom
    ),
    [rev]
    )

     

     

5 Replies

  • pxg08680's avatar
    pxg08680
    Icon for Resolver III rankResolver III
    8 years ago

    dtran

    If you want your Table visual to have only Current Month data, why don't you try using filter/slicer.

    Select the table and in the visual level filter , select the date_daily column and select relative filtering, select last 1 calendar month or last 30 days.

     

    Hope this helps.

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi dtran,

     

    Based on my test, the formula below should work in your scenario. :smileyhappy:

    measure2 =
VAR eom = [measure1]
RETURN
    CALCULATE (
        SUM ( my_table[rev] ),
        FILTER ( my_table, my_table[date_eom] = eom )
    )

     

    Regards

    • dtran's avatar
      dtran
      Icon for Helper II rankHelper II
      8 years ago

      Thanks v-ljerr-msft!

       

      I used your variable suggestion but I also need to use sumx and calculatetable to get the right data.

       

      I believe that if I just use the calculate and then use sum and filter within the calculate funtion, it would still filter out any projected values.

       

      Here's what ended up working for me:

      measure2 =
      VAR eom = [measure1] RETURN
      sumx(
      CALCULATETABLE(
      my_table,
      month(my_table[date_eom])=eom
      ),
      [rev]
      )