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 need help I have PayDate but how will I create other two column as per below: If you can provide me with dax.
Solved! Go to Solution.
Hi, @mdaamirkhan
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
Calendar(a calculated table):
Calendar =
CALENDAR(
DATE(2019,1,1),
DATE(2020,12,31)
)
Year Month(a calculated column):
YearMonth = YEAR([Date])*100+MONTH([Date])
You may create two calculated columns as below.
ResultColumn1 =
var _lastdate =
CALCULATE(
MAX('Table'[PayDate]),
FILTER(
'Table',
'Table'[PayDate]<EARLIER('Table'[PayDate])
)
)
var _lastyearmonth =
CALCULATE(
MAX('Calendar'[Date]),
FILTER(
'Calendar',
'Calendar'[YearMonth]=
CALCULATE(
MAX('Calendar'[YearMonth]),
FILTER(
'Calendar',
[YearMonth]<YEAR([PayDate])*100+MONTH([PayDate])
)
)
)
)
return
IF(
ISBLANK(_lastdate),
FORMAT(_lastyearmonth,"mmmm"),
FORMAT(_lastdate,"mmmm")
)
ResultColumn2 =
var _nextdate =
CALCULATE(
MIN('Table'[PayDate]),
FILTER(
'Table',
[PayDate]>EARLIER('Table'[PayDate])
)
)
return
IF(
ISBLANK(_nextdate),
MONTH([PayDate])+1,
MONTH(_nextdate)
)
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, @mdaamirkhan
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
Calendar(a calculated table):
Calendar =
CALENDAR(
DATE(2019,1,1),
DATE(2020,12,31)
)
Year Month(a calculated column):
YearMonth = YEAR([Date])*100+MONTH([Date])
You may create two calculated columns as below.
ResultColumn1 =
var _lastdate =
CALCULATE(
MAX('Table'[PayDate]),
FILTER(
'Table',
'Table'[PayDate]<EARLIER('Table'[PayDate])
)
)
var _lastyearmonth =
CALCULATE(
MAX('Calendar'[Date]),
FILTER(
'Calendar',
'Calendar'[YearMonth]=
CALCULATE(
MAX('Calendar'[YearMonth]),
FILTER(
'Calendar',
[YearMonth]<YEAR([PayDate])*100+MONTH([PayDate])
)
)
)
)
return
IF(
ISBLANK(_lastdate),
FORMAT(_lastyearmonth,"mmmm"),
FORMAT(_lastdate,"mmmm")
)
ResultColumn2 =
var _nextdate =
CALCULATE(
MIN('Table'[PayDate]),
FILTER(
'Table',
[PayDate]>EARLIER('Table'[PayDate])
)
)
return
IF(
ISBLANK(_nextdate),
MONTH([PayDate])+1,
MONTH(_nextdate)
)
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.
@mdaamirkhan , what is the logic behind it ?
Assuming the date is 10th, create follwing columns
Month Date= If(day([payDate]) <=10 , eomonth([payDate],-1), eomonth([payDate],0))
Month Name = format([Month Date], "MMMM")
Count = countx(filter(table, [Month Date] = earlier([Month Date])),[payDate])
Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.
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 |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |