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.
Hi All,
I am trying to create a DAX formula for a Coulmn (Column C - Meeting Month) that retrives the previous month to the value in another column (Column B - Approval date, However there is a caveat that if the value retrived in "Column C - Meeting Month" is January or July then the value should be amended to retrive the month before those - so December and June, as there are no meetings taking place in January or July.
The ideal formula would displayed the following data in the last column (Column C - Meeting Month):
Solved! Go to Solution.
Hi, @Ankap
Based on your description, I created data to reproduce your scenario.
Table:
'Meeting Month' is a calculated column as below.
Meeting Month = VALUE(FORMAT('Table'[Meeting Date],"yyyymm"))
Then you may create two measures as below.
Approval Date =
var _yearmonth = SELECTEDVALUE('Table'[Meeting Month])
var _lastyearmonth =
CALCULATE(
MAX('Table'[Meeting Month]),
FILTER(
ALL('Table'),
'Table'[Meeting Month]<_yearmonth
)
)
return
IF(
OR(
RIGHT(_yearmonth,2) = "01",
RIGHT(_yearmonth,2) = "07"
),
CALCULATE(
CONCATENATEX('Table','Table'[Meeting Date],","),
FILTER(
ALL('Table'),
'Table'[Meeting Month] = _lastyearmonth
)
),
CALCULATE(
CONCATENATEX('Table','Table'[Meeting Date],","),
FILTER(
ALL('Table'),
'Table'[Meeting Month] = _yearmonth
)
)
)
Approval Month =
var _yearmonth = SELECTEDVALUE('Table'[Meeting Month])
var _lastyearmonth =
CALCULATE(
MAX('Table'[Meeting Month]),
FILTER(
ALL('Table'),
'Table'[Meeting Month]<_yearmonth
)
)
return
IF(
OR(
RIGHT(_yearmonth,2) = "01",
RIGHT(_yearmonth,2) = "07"
),
_lastyearmonth,
_yearmonth
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Ankap
If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.
Best Regards
Allan
Hi, @Ankap
Based on your description, I created data to reproduce your scenario.
Table:
'Meeting Month' is a calculated column as below.
Meeting Month = VALUE(FORMAT('Table'[Meeting Date],"yyyymm"))
Then you may create two measures as below.
Approval Date =
var _yearmonth = SELECTEDVALUE('Table'[Meeting Month])
var _lastyearmonth =
CALCULATE(
MAX('Table'[Meeting Month]),
FILTER(
ALL('Table'),
'Table'[Meeting Month]<_yearmonth
)
)
return
IF(
OR(
RIGHT(_yearmonth,2) = "01",
RIGHT(_yearmonth,2) = "07"
),
CALCULATE(
CONCATENATEX('Table','Table'[Meeting Date],","),
FILTER(
ALL('Table'),
'Table'[Meeting Month] = _lastyearmonth
)
),
CALCULATE(
CONCATENATEX('Table','Table'[Meeting Date],","),
FILTER(
ALL('Table'),
'Table'[Meeting Month] = _yearmonth
)
)
)
Approval Month =
var _yearmonth = SELECTEDVALUE('Table'[Meeting Month])
var _lastyearmonth =
CALCULATE(
MAX('Table'[Meeting Month]),
FILTER(
ALL('Table'),
'Table'[Meeting Month]<_yearmonth
)
)
return
IF(
OR(
RIGHT(_yearmonth,2) = "01",
RIGHT(_yearmonth,2) = "07"
),
_lastyearmonth,
_yearmonth
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Ankap , You need join both the dates to common date dimension and use userelation to activate join
With use relation, you can use Time intelligence lik
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
last QTR same Month (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,Qtr))))
MTD (Year End) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date])))
MTD (Last Year End) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31")))
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 :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
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 |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |