How to get visuals representing different date range based on a slicer ?

[Anonymous]

Hi everybody,

I am dealing with a little problem 

I have a table containing alerts logs and for each line I have a date

I have a date table

Both tables are linked by the date

I am creating a report containing a slicer based on the month from my calendar table
and I would like to get 2 distinct visuals

eg : if january is selected, I would like to get :

- a visual showing the number of alerts in january 
- a visual showing the number of alerts in decembrer 2021

I made :

- a measure "CustomDate" to get the selected month in slicer :

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

- a measure "TotalLinesM0" to get the number of line for the selected month (january) :

TotalLinesM0 =

CALCULATE(

COUNTROWS(ALERTS),

FILTER(

ALERTS,

EOMONTH(ALERTS[DTCREATETIME],0) = EOMONTH([CustomDate], 0)

)

)

- a measure "TotalLinesM-1" to get the number of line for the previous month (december) :

TotalLinesM-1 =

CALCULATE(

COUNTROWS(ALERTS),

FILTER(

ALERTS,

EOMONTH(ALERTS[DTCREATETIME_ST], 0) = EOMONTH([CustomDate], -1)

)

)

My problem is that the measure "TotalLinesM-1" is always empty, surely because my data is already filtered on "january".

And I can't use the ALL function in the filter condition because I want to apply others filters..

Thank you for you time 😊

[Anonymous · Accepted solution]

Hi everyone

I solved my problem. There was no problem with my dataset.
Here is what I did :

I made a measure TotalLinesM0 this way :

TotalLinesM0 =

VAR MyDate_month = MONTH( EOMONTH(MAX(Calendar[Date]), 0))

VAR MyDate_year = YEAR( EOMONTH(MAX(Calendar[Date]), 0))

RETURN

CALCULATE(

COUNTROWS(ALERTS),

MONTH(ALERTS[Date]) = MyDate_month,

YEAR(ALERTS[Date]) = MyDate_year

)

For the M-1 measure I used thre REMOVEFILTERS function :

TotalLinesM-1 =

VAR MyDate_month = MONTH( EOMONTH(MAX(Calendar[Date]), -1))

VAR MyDate_year = YEAR( EOMONTH(MAX(Calendar[Date]), -1))

RETURN

CALCULATE(

COUNTROWS(ALERTS),

REMOVEFILTERS(Calendar[Date].[Mois]),

MONTH(ALERTS[Date]) = MyDate_month,

YEAR(ALERTES[Date]) = MyDate_year

)

Probably not the best way to do it but it but it works 😊
Thanks to all you for your help and for your time.

Regards

[v-yalanwu-msft]

Hi, @Anonymous ;

Is your problem solved? If so, Would you mind accept the helpful replies as solutions? Then we could close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

Best Regards,

Community Support Team_ Yalan Wu

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

[v-yalanwu-msft]

Hi, @Anonymous ;

You could try to use  ALLSELECTED()

TotalLinesM-1 =
CALCULATE (
    COUNTROWS ( ALERTS ),
    FILTER (
        ALLSELECTED ( ALERTS ),
        EOMONTH ( ALERTS[DTCREATETIME_ST], 0 ) = EOMONTH ( [CustomDate], -1 )
    )
)

Or

TotalLinesM-1 =
CALCULATE (
    COUNTROWS ( ALERTS ),
    FILTER (
        ALLSELECTED ( ALERTS ),
        EOMONTH ( ALERTS[DTCREATETIME_ST], 0 ) = EOMONTH ( MAX ( Calendar[Date] ), -1 )
    )
)

 Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi v-yalanwu-msft,

Thanks for your response.

I tried this way too but also doesn't work : my TotalLinesM-1 measure is always empty.

I must do something wrong : I will check my datas. It might be a problem with the change of year and the date calculation.

I will let you know 

Thanks

[lbendlin]

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

can be rewritten as

CustomDate = MAX(Calendar[Date])

as it automatically only consideres the selected/filtered date range. Next you want to "pin" that value and protect it from context transitions.

TotalLinesM0 =
var CustomDate = MAX(Calendar[Date])
RETURN CALCULATE(COUNTROWS(ALERTS),
EOMONTH(ALERTS[DTCREATETIME],0) = EOMONTH([CustomDate], 0)
)

TotalLinesM-1 =
var CustomDate = MAX(Calendar[Date])
RETURN CALCULATE(COUNTROWS(ALERTS),
EOMONTH(ALERTS[DTCREATETIME_ST], 0) = EOMONTH([CustomDate], -1)
)

[Anonymous]

Hi Ibendlin,

Sorry for the late answer I was off for a few days.

I tryed the way you told me but it didn't work.

1- No problem to redefine the CustomDate it works fine

2- I can't define the measure TotalLinesM0 and TotalLinesM-1 as you told me : 

TotalLinesM0 =
var CustomDate = MAX(Calendar[Date])
RETURN CALCULATE(COUNTROWS(ALERTS),
EOMONTH(ALERTS[DTCREATETIME],0) = EOMONTH([CustomDate], 0)
)
--> I have the following error : "A "PLACEHOLDER" function was used in a True/False expression used as a table filter expression. This is not allowed."
So I use the syntax I first described

3- In this configuration the TotalLinesM-1 measure is always empty.
The "var CustomDate = MAX(Calendar[Date])" statement doesn't seem to be interpreted


I am trying to go on using the ALLEXEPT and ALLCROSFILTER functions.

Thanks for your time