Dax measure to exclude current month

[Anonymous]

Hi 

I have a line graph which has to measures in it to show the trend for Penetration - however i need to duynamically exclude current month 
is there a way to do that?

This is the original measure and the same is done for UK 
IE Pen = DIVIDE(SUM(' IE'[Sales ]),SUM(' IE'[reg]),0)

[isabella · Accepted solution]

Hi @Anonymous 

I did a local test ,you can take a reference 

Here is my test  table

First I created a calendar table

Then create a one-to-one relationship between these two tables

Then I create the measure

IE Pen = DIVIDE(SUM('Table'[sales]),SUM('Table'[reg]),0)

Then I create the measure

Measure = CALCULATE([IE Pen],FILTER('calendar', (10*YEAR('calendar'[Date])+'calendar'[Date].[MonthNo]) < (10*YEAR(TODAY())+MONTH(TODAY()))))

Then in visualization，I create a line chart ：

We can see from the line chart ，there is no data in June  2022

If we change the graph  to table，we can  also see that  the current month data is excluded

Best Regards,

Community Support Team _Isabella

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@isabella Hi thanks for your help
The formula worked perfectly - however im trying to replicate it for UK now and UK pen is exactly the same as IE pen formula however it will not exclude current month

[Anonymous]

[rajulshah]

Hello @Anonymous ,

Please try the following measure and let me know if it works:

IE Pen Current Month = 
CALCULATE(
    [IE Pen], 
    FILTER('Calendar',MONTH('Calendar'[Dates]) = MONTH(TODAY())),
    FILTER('Calendar',YEAR('Calendar'[Dates]) = YEAR(TODAY()))
    )

[Anonymous]

Hi - no that does work thank you 

I need the measure to show everything except for Current month