DAX - Using Filter to Sum All Months by Working Day

[Agreenwood]

Hi,

Fairly new to DAX and been frustrated all day trying to figure this one out. There is probably a simple answer.

I have a 'Dates' table which has 2 columns: 1) 'Date' (2) 'Working Day' which holds the working day number of the month

I have a second table 'Revenue' which has 2 columns: 1) 'Date' (2) 'Daily Revenue'

There is a relationship between the two tables, on date.

I want to display in a report a filter on "Working Day", and a table/graph showing monthly revenue for all days up until the working day that is selected in the filter.

In Excel: =SUMIFS(Revenues[Revenue], Dates[Working Day], "<=" & SelectedWorkingDay)

In Power BI I have attempted many variations and combinations of failing measures, though my closest attempt is:

MtD Revenue = CALCULATE(SUM(Revenues[Revenue]), FILTER(ALL(Dates), Dates[workingday] <= MAX(Dates[workingday])))

However, this sums all revenue for all months before the selected working day, and does not sum the revenue for the individual month in the visualisation.

I have also attempted to use the EARLIER() function in a calculated column, though using this solution I can not use the working day filter on the dashboard.

Thank you in advance for your help... 

[Agreenwood]

Ok, I think I may have answered my own question with the following measure:

MtD Revenue = CALCULATE(SUM(Revenue[Revenue]), FILTER(ALL(Dates), Dates[workingday] <= MAX(Dates[workingday])), FILTER(ALL(Dates), Dates[Date] <= MAX(Revenue[date])))

Are there any alternative ways of completing the same, or better ways maybe?

[v-jiascu-msft · Accepted solution]

Hi @Agreenwood,

According to your formulas, it seems the monthly revenue will be accumulated. Maybe you can try these formula below. You can check it out in this file. 

Measure 2 =
TOTALYTD (
    SUM ( Revenues[Revenue] ),
    Dates[Date],
    FILTER ( ALL ( 'Dates' ), 'Dates'[WorkingDay] <= MAX ( Dates[WorkingDay] ) )
)

Measure =
TOTALMTD ( SUM ( Revenues[Revenue] ), 'Dates'[Date] )

Best Regards,

Dale