cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Treawlony Frequent Visitor
Frequent Visitor

Previous month based on slicer

HI All,

I need to have one graph of a report page that shows data of a month AFTER the one selected in the slicer

 

i.e: I select SEPTEMBER, I see SEPTEMBER data on the page, but one graph must show OCTOBER one.

 

I can't find any way to do that and also can't ifnd a way to use DAX

 

Anyone can help me?

 

Thanks

10 REPLIES 10
Watsky Regular Visitor
Regular Visitor

Re: Previous month based on slicer

You could make a calculated column with the function DATEADD to add another month to your date column then in your graph replace the original date with the new calculated column.

 

Next Month =
	DATEADD ( [DATE], 1, MONTH )
Treawlony Frequent Visitor
Frequent Visitor

Re: Previous month based on slicer

This would not work, because the graph itself has no date filter in it, it's on a page level.

So You would just swith che problem to all other graphs, that should be one month back 🙂

 

On a logic perspective, I just need that the graph shows me the data of the NEXT month I select in the general slicer

 

Watsky Regular Visitor
Regular Visitor

Re: Previous month based on slicer

Is the date is not on your graph as an axis?  Do you just have values and other attributes on the axis? Can you screenshot your graph?

 

 

Treawlony Frequent Visitor
Frequent Visitor

Re: Previous month based on slicer

no, it's gauges, can't share screenshots are there are sensible data on it

amitchandak Super Contributor
Super Contributor

Re: Previous month based on slicer

In visual level filter, you have the option for the relative date you can choose the next 1 month

 

Or you can try these formula's

Next MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date Filer],1,MONTH)))



Next month = 
Var _end_date= minx(ALLSELECTED('Date'),ENDOFMONTH(dateadd('Date'[Date Filer],1,MONTH)))
Var  _start_date =minx(ALLSELECTED('Date'),STARTOFMONTH(dateadd('Date'[Date Filer],1,MONTH)))

Var _last_year_mtd_val= CALCULATE(sum(Sales[Sales Amount]),Sales[Sales Date] >= _start_date && (Sales[Sales Date]) <= _end_date)
return
_last_year_mtd_val

Screenshot 2019-10-11 21.37.33.png

Treawlony Frequent Visitor
Frequent Visitor

Re: Previous month based on slicer

My god this forum, 3rd time I reply and I get an error

 

So... still not good as you apply "static DAX" or something non-slicer related.

I need that the gauge dynamically shows me the next month of the slicer date I select, so if I select Jaunary it shows me February and if I change to March it shows me April.

 

I find strange that MS has not implemented something that is slicer-dependant

Community Support Team
Community Support Team

Re: Previous month based on slicer

Hi @Treawlony ,

 

We can create a calculated table contain the data in the single visual, then create a measure using following measure.

 

isInNextMonth = 
VAR tMonth =
    MAX ( 'Table'[Date] )
VAR nMonth =
    IF (
        MONTH ( tMonth ) = 12,
        DATE ( YEAR ( tMonth ) + 1, 1, 1 ),
        DATE ( YEAR ( tMonth ), MONTH ( tMonth ) + 1, 1 )
    )
RETURN
    IF (
        YEAR ( max ( 'Table-Copy'[Date] ) ) = YEAR ( nMonth )
            && MONTH (max ( 'Table-Copy'[Date] ) ) = MONTH ( nMonth ),
        1,
        0
    )

put it in the Visual Filter, set condition is 1.

 

19.PNG

 


BTW, pbix as attached.

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Treawlony Frequent Visitor
Frequent Visitor

Re: Previous month based on slicer

mmmm interesting, let me try it and you might win a solution 😄

Treawlony Frequent Visitor
Frequent Visitor

Re: Previous month based on slicer

not really make it work, but got few time to work on it lately

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)