Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a visual that needs to be filtered to the latest quarter with a condition as follows.
If the difference between current date and last date of the previous quarter is less than a month, previous quarter and the remaining days in this quarter should be considered. Otherwise current quarter should be considered.
Could you please help me on how to achieve this task? Thanks in advance
Solved! Go to Solution.
Hi @Anonymous ,
Please use :
test =
VAR monthafterlastquarter =
MOD ( MONTH ( TODAY () ), 3 )
VAR quartartoday =
QUARTER ( TODAY () )
VAR firstdayoflastquarter =
IF (
quartartoday = 1,
CALCULATE (
MIN ( Dim_Date1[Date] ),
FILTER (
Dim_Date1,
Dim_Date1[Q] = 4
&& YEAR ( Dim_Date1[Date] )
= YEAR ( TODAY () ) - 1
)
),
CALCULATE (
MIN ( Dim_Date1[Date] ),
FILTER (
Dim_Date1,
Dim_Date1[Q] = quartartoday - 1
&& YEAR ( Dim_Date1[Date] ) = YEAR ( TODAY () )
)
)
)
RETURN
IF (
monthafterlastquarter >= 1,
CALCULATE (
[utilallocvariation],
FILTER (
Dim_Date1,
Dim_Date1[Q] = quartartoday
&& YEAR ( Dim_Date1[Date] ) = YEAR ( TODAY () )
)
),
CALCULATE (
[utilallocvariation],
FILTER (
Dim_Date1,
Dim_Date1[Date] >= firstdayoflastquarter
&& Dim_Date1[Date] <= TODAY ()
)
)
)
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Hi @Anonymous ,
First, you need to create a dim_date table in your date model:
Dim_Date = ADDCOLUMNS(CALENDAR(DATE(2021,1,1),DATE(2021,12,31)),"Month",MONTH([Date]),"Q",FORMAT([Date],"q"))
Then you can use the following measure :
Measure =
VAR monthafterlastquarter =
MOD ( MONTH ( TODAY () ), 3 )
VAR quartartoday =
QUARTER ( TODAY () )
VAR firstdayoflastquarter =
IF (
quartartoday = 1,
CALCULATE (
MIN ( Dim_Date[Date] ),
FILTER (
Dim_Date,
Dim_Date[Q] = 4
&& YEAR ( Dim_Date[Date] )
= YEAR ( TODAY () ) - 1
)
),
CALCULATE (
MIN ( Dim_Date[Date] ),
FILTER (
Dim_Date,
FILTER (
Dim_Date,
Dim_Date[Q] = quartartoday - 1
&& YEAR ( Dim_Date[Date] ) = YEAR ( TODAY () )
)
)
)
)
RETURN
IF (
monthafterlastquarter >= 1,
CALCULATE (
[Your Measure],
FILTER (
Dim_Date,
FILTER (
Dim_Date,
Dim_Date[Q] = quartartoday
&& YEAR ( Dim_Date[Date] ) = YEAR ( TODAY () )
)
),
CALCULATE (
[Your Measure],
FILTER (
Dim_Date,
Dim_Date[Date] >= firstdayoflastquarter
&& Dim_Date[Date] <= TODAY ()
)
)
)
)
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Hi @v-deddai1-msft ,
Thank you very much for the reply. I am getting following error.
The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.
Could you please check?
Hi @Anonymous ,
Would you please try to change the dim_date calculated table to:
Dim_Date = ADDCOLUMNS(CALENDAR(DATE(2021,1,1),DATE(2021,12,31)),"Month",MONTH([Date]),"Q",QUARTER([Date]))
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Hi @Anonymous ,
Please use the following measure:
Measure =
VAR monthafterlastquarter =
MOD ( MONTH ( TODAY () ), 3 )
VAR quartartoday =
QUARTER ( TODAY () )
VAR firstdayoflastquarter =
IF (
quartartoday = 1,
CALCULATE (
MIN ( Dim_Date[Date] ),
FILTER (
Dim_Date,
Dim_Date[Q] = 4
&& YEAR ( Dim_Date[Date] )
= YEAR ( TODAY () ) - 1
)
),
CALCULATE (
MIN ( Dim_Date[Date] ),
FILTER (
Dim_Date,
Dim_Date[Q] = quartartoday - 1
&& YEAR ( Dim_Date[Date] ) = YEAR ( TODAY () )
)
)
)
RETURN
IF (
monthafterlastquarter >= 1,
CALCULATE (
[Your Measure],
FILTER (
Dim_Date,
Dim_Date[Q] = quartartoday
&& YEAR ( Dim_Date[Date] ) = YEAR ( TODAY () )
),
CALCULATE (
[Your Measure],
FILTER (
Dim_Date,
Dim_Date[Date] >= firstdayoflastquarter
&& Dim_Date[Date] <= TODAY ()
)
)
)
)
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Hi @Anonymous ,
Show me your pbix file.
Best Regards,
Dedmon Dai
Hi @v-deddai1-msft ,
Here is pbix file. I created Dim_Date1 table, as I already have Dim_Data table. Inside Dim_Date1 table I created the measure called test. I have placed the visual in the file. Appreciate, if you could add the logic to existing Dim_Date table.
Thanks and regards,
Chulendra
Hi @Anonymous ,
Please use :
test =
VAR monthafterlastquarter =
MOD ( MONTH ( TODAY () ), 3 )
VAR quartartoday =
QUARTER ( TODAY () )
VAR firstdayoflastquarter =
IF (
quartartoday = 1,
CALCULATE (
MIN ( Dim_Date1[Date] ),
FILTER (
Dim_Date1,
Dim_Date1[Q] = 4
&& YEAR ( Dim_Date1[Date] )
= YEAR ( TODAY () ) - 1
)
),
CALCULATE (
MIN ( Dim_Date1[Date] ),
FILTER (
Dim_Date1,
Dim_Date1[Q] = quartartoday - 1
&& YEAR ( Dim_Date1[Date] ) = YEAR ( TODAY () )
)
)
)
RETURN
IF (
monthafterlastquarter >= 1,
CALCULATE (
[utilallocvariation],
FILTER (
Dim_Date1,
Dim_Date1[Q] = quartartoday
&& YEAR ( Dim_Date1[Date] ) = YEAR ( TODAY () )
)
),
CALCULATE (
[utilallocvariation],
FILTER (
Dim_Date1,
Dim_Date1[Date] >= firstdayoflastquarter
&& Dim_Date1[Date] <= TODAY ()
)
)
)
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
@Anonymous , a measure like
measure =
var _month = mod(month(today(),3)
var _end = eomonth(date(year(today()), month(today()) -1*_month, 1),0)
var _diff =datediff(_month, _end, month)
return
if(_diff>1, CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
, CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER))) )
Hi @amitchandak ,
Thank you very much for the reply. I am not getting expected results. I think problem is with false term in the if clause.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |