Calculating target number based on day in month

[mvyskala]

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

[Reid_Havens]

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

Havens Consulting Inc.

[mvyskala]

Hi @Reid_Havens and @Phil_Seamark, thanks a ton for your swift reply. I'll get to this issue in my business hours (CET) and I'll get back to you. Seems like I should be able to find solution from your ideas.

 

Many thanks

MV

[v-ljerr-msft]

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

[Phil_Seamark · Accepted 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))

[mvyskala]

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.