Sales for Current Month & Auto select current month in slicer

[Anonymous]

Hello Power BI Community,

Attached is a simple pbix for sales by month. I am trying to show sales for the latest month in the file using DAX.  I have created 2 measures:

1) To get current month:

Current Month = max('Calendar'[year_month])

2) To get sales for current month

sales for current month = CALCULATE(sum(Sales[Total]),FILTER('Calendar','Calendar'[year_month]=[Current Month]),FILTER(Sales,Sales[Measure For Month]="Sales"))

Without anything selected in the slicer I get the total sales displayed and not just for the current month.  What is the correct DAX to get the latest sales data?

Also would like to know if there is a way to auto select the year_month filter with the latest year_month. For example in the attached dataset Sept 2018 should be auto selected.  If October data is added then Oct 2018 should be auto selected. 

Any help with either of these questions will be appreciated!

Sample File

[Anonymous]

I have a pivot table with balances for each month.  The issue is that my balances are already cumulative, so when I drill up to the Year, quarter, it's summing the balances and I don't want them to sum. I only want to show the balances for each month and when I drill up to the full year or latest quarter, it should only show the most recent month's balances because as I mentioned they are already cumulative each  month.  Any help would be greatly appreciated!

[MFelix · Accepted solution]

Hi @Anonymous,

Looking at your data you need to change your measure to:

sales for current month =
VAR Current_Month = [Current Month]
RETURN
    CALCULATE (
        SUM ( Sales[Total] );
        FILTER (
            ALL ( 'Calendar'[year_month] );
            'Calendar'[year_month] = Current_Month
        );
        Sales[Measure For Month] = "Sales"
    )

When you are calculating a measure the several values are calculated one after the other so placing the [Current Month] within your filter this would be calculated after the SUM of sales so you would get first the sum of each month and then filter the month however it was for the full table so it would work as the table you have in your report so 2.100. 

Making a variable with the [Current Month] the first calculation is this measure and then this value will be used on your final result so only values of september (or selected month) is fetech to be calculated.

Attach PBIX.

Regards,

MFelix

[Auwwaya]

I think, This happens not because of the calculation order, but because of the context transition. (In calculate, the filter is evaluated before the expression is calculated). Measures (in here "current month") do context transition. Therefore, when the filter criteria are evaluated, the row context is transitioned into a filter context and "max" is evaluated in a single row. So all rows satisfy the filter criteria. You can clearly see when you do the iteration within the filter.

Cur Mo Sales = 
CALCULATE(
     SUM(Sales[Total]),
     FILTER(
         all('Calendar'),
         'Calendar'[year_month]=MAX('Calendar'[year_month])),
         Sales[Measure For Month]="sales"
)

Then you get the correct result, as the aggregators don't do context transition. (unlike measures) Then you get the correct result.

[Yindiao]

Hi , @MFelix 

Thanks for the tips on Current Month, how about Previous Month?

I tried to change the DAX to something like below, but it's showing me an error when I tried to pull the measurement to dashboard.

First create:

Prior Month = max('Calendar'[year_month])-1





Then create:



sales for prior month =

var Prior_Month = [Prior Month]

return

CALCULATE (

SUM ( Sales[Total] ),

FILTER (

ALL ( 'Calendar'[year_month] ),

'Calendar'[year_month] = Prior_Month

),

Sales[Measure For Month] = "Sales"

)

[Yindiao]

Hi  ,

Thanks for the tips on Current Month, how about Previous Month?

I tried to change the DAX to something like below, but it's showing me an error when I tried to pull the measurement to dashboard.

First create:

Prior Month = max('Calendar'[year_month])-1

Then create:

sales for prior month =

var Prior_Month = [Prior Month]

return

CALCULATE (

    SUM ( Sales[Total] ),

    FILTER (

        ALL ( 'Calendar'[year_month] ),

        'Calendar'[year_month] = Prior_Month

    ),

    Sales[Measure For Month] = "Sales"

)

[Anonymous]

Hi @MFelix,

That seems to do the trick. I still need to work on understanding the order of filters especially when they are nested.  Any tips / suggestions on getting the date slicer to automatically select the current month / year (i.e. last month/year with the data) ?