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 am struggling with this one - I can do current year and prior for days but for the financials I need months and not include the current month so we dont get hals months at the start and end.
I have a table with weekending dates and the month it relates and this table is linked to the main invoice data set.
In the dates table I need to say for example (in Excel):
=IF(AND(V2<EOMONTH(TODAY(),-1)+1,V2>EDATE(EOMONTH(TODAY(),-1)+1,-13)),"Current Year",IF(AND(V2<EDATE(EOMONTH(TODAY(),-1)+1,-12),V2>EDATE(EOMONTH(TODAY(),-1)+1,-25)),"Prior Year","-"))
My table is called "Month Mapping" and simply has 3 columns:
Any help truly appreciated
Solved! Go to Solution.
Hi @MITeam ,
Here are the steps you can follow:
1. Create calculated column.
Column =
var _today=TODAY()
var _currentend=
EOMONTH(
_today,-1)
var _currentstart=
EOMONTH(
_today,-13)
var _prioryear=
EOMONTH(
_currentstart,-12)
return
IF(
'Month Mapping'[W/E] >_prioryear&&'Month Mapping'[W/E] <=_currentstart,
"Prior Year",
IF(
'Month Mapping'[W/E]>_currentstart&&'Month Mapping'[W/E]<=_currentend,"Current Year",
"-"
))
2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @MITeam ,
Here are the steps you can follow:
1. Create calculated column.
Column =
var _today=TODAY()
var _currentend=
EOMONTH(
_today,-1)
var _currentstart=
EOMONTH(
_today,-13)
var _prioryear=
EOMONTH(
_currentstart,-12)
return
IF(
'Month Mapping'[W/E] >_prioryear&&'Month Mapping'[W/E] <=_currentstart,
"Prior Year",
IF(
'Month Mapping'[W/E]>_currentstart&&'Month Mapping'[W/E]<=_currentend,"Current Year",
"-"
))
2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Thanks Liu. I realised that the excel formula I put in asking for help worked lol.. People must have read and thought I was a plank:
Anid if a genius can also give me another column to go with this of Last Month and Prior Year Last month so currently it would give Feb 2023 and Feb 2022 I think I would cry with Happiness.
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 |
---|---|
108 | |
98 | |
81 | |
65 | |
62 |
User | Count |
---|---|
147 | |
116 | |
104 | |
88 | |
65 |