cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sensei Regular Visitor
Regular Visitor

Sales for Current Month & Auto select current month in slicer

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Sales for Current Month & Auto select current month in slicer

Hi @sensei,

 

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




4 REPLIES 4
Super User
Super User

Re: Sales for Current Month & Auto select current month in slicer

Hi @sensei,

 

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




sensei Regular Visitor
Regular Visitor

Re: Sales for Current Month & Auto select current month in slicer

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) ?

Yindiao Frequent Visitor
Frequent Visitor

Re: Sales for Current Month & Auto select current month in slicer

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"

 

)

 

 

Yindiao Frequent Visitor
Frequent Visitor

Re: Sales for Current Month & Auto select current month in slicer

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"

)