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.
Hello,
I'd like to in my view calculate column or measure based on which day of month. For example I have target number for year 2017 12.000. If today is day before 16th day in current month, the target should be based on end of previous month, but if the day is 16th or after in current month, target should be based on end of current month.
So in this example, if the day is 15th March target should be 2.000 (target for February), if the day is 16th March, target should be 3.000.
Any idea how to do this without using to many IF functions?
Thanks in advance
MV
Solved! Go to Solution.
Hi @mvyskala
What happens if you add this calcuated column to your table?
Target = (12000 / 12 ) * (MONTH('Table'[your date col]) + IF(DAY([your date col])>=16,1,0))
Hi @mvyskala,
IF is certainly the easiest way to do this, it shouldn't be a very long formula either. Something like below should work for you. Is there any reason you're wanting to avoid IF statements?
= IF ( DAY ( TODAY () ) <= 16, CALCULATE ( [Sum of Target], PREVIOUSMONTH ( 'Date Table'[Date] ) ),
[Sum of Target] )
Best Regards,
Reid Havens - Owner / Founder
Hi @mvyskala,
Have you tried the solutions provided above? If you still have any problem, feel free to post here.
If the solutions work for you, could you accept the helpful reply as answer to help others who may have similar issue easily find the answer and close this thread?
Regards
Hi @mvyskala
What happens if you add this calcuated column to your table?
Target = (12000 / 12 ) * (MONTH('Table'[your date col]) + IF(DAY([your date col])>=16,1,0))
Thanks for all inputs. I did something similar as @Phil_Seamark offered. My final solution was basically something like this
Target = (([EndOfFiscalYearTarget] / 12 ) * ((MONTH('Table'[Today]))-3)) + IF([DayToday]>16;([EndOfFiscalYearTarget]/12);0)
I've creared column for Today and for Today.[Day] and I'm also offsetting 3 months because of start of fiscal year.
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 |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |