Cal.Column Filter for Current Month, Last 3 month from today, Next 3month from Today

[mogunase1]

Hi,

I need a filter in which it need to show ,

1. Current Month ( i.e., July-2021)

2. Last 3 Month ( i.e., Apr-21,May-21,June-21)

3. Next 3 Month ( i.e., Aug-21,Sep-21,Oct-21).

I used the below measure as Calculated Column, but it showing for all the year in data. Please help here.

Month Selection = Switch
(true(),
MONTH(Calender[Date])=MONTH(TODAY()),"This Month",
QUARTER(Calender[Date])=QUARTER(Today())-1,"Last 3 Month",
QUARTER(Calender[Date])=QUARTER(TODAY())+1,"Next 3 MOnth",
FORMAT([Date],"MMM")
)

Note:

Im not calculating any Value, im using Gantt Chart here.. Im using Start and End Date.

So i need Calculated column to use in Filter pane.

Sample file attached here,

https://www.dropbox.com/s/r24tcz8t335yp0v/Gantt%20Visual.pbix?dl=0

Thanks,

Mohan

@Fowmy  @amitchandak @Jihwan_Kim @parry2k 

[Jihwan_Kim]

Value total : =

SUM(Data[Value])

Value total by slicer : =

VAR _currentmonthend =

EOMONTH ( TODAY (), 0 )

VAR _onemonthagomonthend =

EOMONTH ( TODAY (), -1 )

VAR _threemonthagomonthend =

EOMONTH ( TODAY (), -3 )

VAR _onemonthlatermonthend =

EOMONTH ( TODAY (), 1 )

VAR _threemonthlatermonthend =

EOMONTH ( TODAY (), 3 )

RETURN

SWITCH (

SELECTEDVALUE ( Slicer[Index] ),

1, CALCULATE ( [Value total :], Dates[End of Month] = _currentmonthend ),

2,

CALCULATE (

[Value total :],

KEEPFILTERS (

FILTER (

ALL ( Dates ),

Dates[End of Month] >= _threemonthagomonthend

&& Dates[End of Month] <= _onemonthagomonthend

)

)

),

3,

CALCULATE (

[Value total :],

KEEPFILTERS (

FILTER (

ALL ( Dates ),

Dates[End of Month] >= _onemonthlatermonthend

&& Dates[End of Month] <= _threemonthlatermonthend

)

)

),

CALCULATE ( [Value total :], Dates[End of Month] = _currentmonthend )

)

Link to the pbix file 

[mogunase1 · Accepted solution]

Thanks Everyone for Reply.

Finally i got what i need,below are the Calculated column which i expected.

Month filter =

var currentdate = TODAY()

var lastmonth = DATE(YEAR(currentdate),MONTH(currentdate)-3,DAY('Table'[Date]))
var lastmonth1 = DATE(YEAR(currentdate),MONTH(currentdate)-2,DAY('Table'[Date]))
var lastmonth2 = DATE(YEAR(currentdate),MONTH(currentdate)-1,DAY('Table'[Date]))
var thismonth = DATE(YEAR(currentdate),MONTH(currentdate),DAY('Table'[Date]))
var upmonth = DATE(YEAR(currentdate),MONTH(currentdate)+3,DAY('Table'[Date]))
var upmonth1 = DATE(YEAR(currentdate),MONTH(currentdate)+2,DAY('Table'[Date]))
var upmonth2 = DATE(YEAR(currentdate),MONTH(currentdate)+1,DAY('Table'[Date]))

var last_3_month = SWITCH(
TRUE(),
Table[Date]=lastmonth,"last month",Table[Date]=lastmonth1,"last2month",Table[Date]=lastmonth2,"last 3month")

var Next_3_month = SWITCH(
TRUE(),
Table[Date]=upmonth,"up month",Table[Date]=upmonth1,"next2month",Table[Date]=upmonth2,"next 3month")

var combine = IF(ISTEXT(last_3_month),"Last 3 Month",IF(ISTEXT(Next_3_month),"Next 3 Month",IF(Table[Date]=thismonth,"This Month","Others")))

return combine

@Preeti_Yadav @Jihwan_Kim @amitchandak 

[mogunase1]

Hi,

Thanks for your reply.

But this is not actually im looking for,

i attached the sample file below for your clear understand.

If possible can you check and suggest.

https://www.dropbox.com/s/r24tcz8t335yp0v/Gantt%20Visual.pbix?dl=0

Thanks,

Mohanraj

[Preeti_Yadav]

Hi @mogunase1 ,

Please try creating the follwing measure that might solve your issue:

Measure for -3Months:

-3 Months =

var currentdate = TODAY()

var previousdate = DATE(YEAR(currentdate),MONTH(currentdate)-5,DAY(currentdate))

var cal = CALCULATE(SUM('Column Name’)FILTER('Calendar','Calendar'[Date]>= previousdate && 'Calendar'[Date]<= currentdate))

return cal

 Measure for +3Months:

+3 Months =

var currentdate = TODAY()

var previousdate = DATE(YEAR(currentdate),MONTH(currentdate)+3,DAY(currentdate))

var cal = CALCULATE(SUM(DATA[AED_AMT]),FILTER('Calendar','Calendar'[Date]<= previousdate && 'Calendar'[Date]>=currentdate))

return cal

Best Regards,
Preeti

Did I answer your question? Mark my post as a solution!Give Kudos!!

[Preeti_Yadav]

Hi @mogunase1 

Some changes in my previous measure,please try this:

Last 3 Month ( i.e., Apr-21,May-21,June-21)

-3 Months =

var currentdate = TODAY()

var previousdate = DATE(YEAR(currentdate),MONTH(currentdate)-3,DAY(currentdate))

var cal = CALCULATE(SUM('Column Name’)FILTER('Calendar','Calendar'[Date]>= previousdate && 'Calendar'[Date]<= currentdate))

return cal

  Next 3 Month ( i.e., Aug-21,Sep-21,Oct-21)

+3 Months =

var currentdate = TODAY()

var previousdate = DATE(YEAR(currentdate),MONTH(currentdate)+3,DAY(currentdate))

var cal = CALCULATE(SUM(DATA['Column_name']),FILTER('Calendar','Calendar'[Date]<= previousdate && 'Calendar'[Date]>=currentdate))

return cal

Best Regards,
Preeti

Did I answer your question? Mark my post as a solution!Give Kudos!!

[amitchandak]

@mogunase1 , with help from time intelligence and date table

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),-1),-3,MONTH))

Rolling 3 next= CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),0)+1,3,MONTH))

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.

[mogunase1]

Hi, 

Actually i dont need to calculate value..

Calculation based on Start and End Date , here im Using Gant Chart..

So i need measure to use in Filter.