How to create a measure relative to date slicer to filter data out of the slicer scope ?

[Anonymous]

Hi everybody,

Brand new to Power BI I have been strugling with my problem for 2 weeks now.

This is my 1st post to the Comumunity, hope someone could help, and sorry for my english (not my native langage 😅)

My problem is in the subject : I would like to create a measure relative to date slicer in order to filter data out of the slicer scope.

I have a table containing server data logs.

I need to build a report containing differents graphics

My report contains a date based slicer

Some of the graphics have to show data from previouses monthes relative to the date picked in the slicer

Eg: if "April" is picked in the slicer, my graphic has to show the number of rows relative to March, if "March" is picked in the slicer, my graphic has to show the number of rows relative to February, and so on

I created a mesure to get the selected month from the slicer :

CustomDate = MAX(Table[Date])

and another one to get the number of rows based on the CustomDate measure (less one month) :

CustomDatePreviousMonthTotalLines =

CALCULATE(

COUNTROWS(Table),

FILTER(

Table,

MONTH(Table[Date]) = MONTH([CustomDate]) -1

)

)

But this doesn't work, may be I have to use some yet unknown functions..

Thanks for you help, thanks for your time 🙂

[amitchandak · Accepted solution]

@Anonymous , Try like

CustomDate = MAXX(allselected(Table), Table[Date])

CustomDatePreviousMonthTotalLines =
CALCULATE(
COUNTROWS(Table),
FILTER(
Table,
EOMONTH(Table[Date],0) = Eomonth([CustomDate], -1)
)
)

But you might need all

CustomDatePreviousMonthTotalLines =
CALCULATE(
COUNTROWS(Table),
FILTER(
all(Table),
EOMONTH(Table[Date],0) = Eomonth([CustomDate], -1)
)
)

-----

These calculation best work with date table
CustomDate = MAXX(allselected(Date), Date[Date])

CustomDatePreviousMonthTotalLines =
CALCULATE(
COUNTROWS(Date),
FILTER(
all(Date),
EOMONTH(Date[Date],0) = Eomonth([CustomDate], -1)
)
)

Better to use time intelligence

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[Anonymous]

Hi,

Thanks for your help it's work fine 👍

I will take a look to the links you mentionned