Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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
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
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!!
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!!
@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.
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
84 | |
84 | |
65 | |
62 | |
55 |
User | Count |
---|---|
171 | |
110 | |
109 | |
73 | |
71 |