Make the matrix column interactive by defining what is current month

[Anonymous]

Hi all,

I have a Matrix visual in which it shows the following as columns:

Previous Months

Current Month

Month +1

Month +2

Month +3

Month +4

This of course based on current month and has values underlying in the matrix for each month.

In this case, I want the client to be able to change the view and select what he sees as a current month. So for example define that the next month would be seen as current month. Is this possible to do in Power BI?

Thanks!

[v-kkf-msft · Accepted solution]

Hi @Anonymous ,

I create the sample data.

Then create these measures.

Previous Months = 
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER ( ALL ( Dates ), Dates[Date] < MIN ( Dates[Date] ) )
)

Current Month = 
SUM ( 'Table'[Value] )

Month +1 = 
CALCULATE ( [Current Month], NEXTMONTH ( Dates[Date] ) )

Month +2 = 
CALCULATE ( [Month +1], NEXTMONTH ( Dates[Date] ) )

Month +3 = 
CALCULATE ( [Month +2], NEXTMONTH ( Dates[Date] ) )

Month +4 = 
CALCULATE ( [Month +3], NEXTMONTH ( Dates[Date] ) )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , You can measure like

This month Today =
var _min = eomonth(today(),-1)+1
var _max = eomonth(today(),0) //today()
return
CALCULATE(sum('Table'[Qty]), FILTER(('Date'),'Date'[Date] >= _min && 'Date'[Date] <=_max ) )

Last month Today =
var _min = eomonth(today(),-2)+1
var _max = eomonth(today(),-1) //today()
return
CALCULATE(sum('Table'[Qty]), FILTER(('Date'),'Date'[Date] >= _min && 'Date'[Date] <=_max ) )

Last to Last month Today =
var _min = eomonth(today(),-3)+1
var _max = eomonth(today(),-2) //today()
return
CALCULATE(sum('Table'[Qty]), FILTER(('Date'),'Date'[Date] >= _min && 'Date'[Date] <=_max ) )

based on selected data

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

previous month value = CALCULATE(sum('Table'[total hours value]),previousmonth('Date'[Date]))

2nd

last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-2,MONTH)))

previous month value = CALCULATE(sum('Table'[total hours value]),previousmonth(dateadd('Date'[Date],-1,MONTH)))

Or

Last 5 month Today =
var _min = eomonth(today(),-5)+1
var _max = eomonth(today(),0) //today()
return
CALCULATE(sum('Table'[Qty]), FILTER(('Date'),'Date'[Date] >= _min && 'Date'[Date] <=_max ) )