Assign Number to A ranges of Dates

DAX DAX Calculations
1

Hi DNA team,

I would like to Assign A Number to Particular Date based on the month in my Table.

Ideally a calculated column that complete this tasks:

1 = All Dates within the Month of (May)
2 = All Dates within the Month of (June)
3 = All Dates within the Month of (July)
4 = All Dates within the Month of (Aug)
5 = All Dates within the Month of (Sept)

Etc.

Sales.pbix (71.2 KB)

Any help you can provide with be appreciated. All Dates are listed in the Part 1 Out Column.

Adam

2

Hi,

You can give a try to MONTH() function in if else logic .
It will solve your problem.

Thanks,
Anurag

3

Hi @adam,

@Anurag is correct, you can use the MONTH() to solve this…

Add this as a calculated column.

Assign number = 
VAR myMonth = MONTH(Sales[Part 1 Out])
RETURN

IF( myMonth >= 5, myMonth-4,  12-(4-myMonth))

I hope this is helpful.

4

OK Thanks Melissa,

I try that now!

KR

Adam

5

Hi Melissa,

MONTH IntelliSense doesn’t seem to pickup the Part 1 Out Column? Its definitely a date column.

Kind regards

Adam

6

If it’s not picking it up you are probably creating a measure and not a calculated column…

Here’s your sample file incl the added Calculated Column
Sales.pbix (72.5 KB)

image
image869×332 21.1 KB

7

Hi Melissa,

This Logic provided above appears to be resetting for May 2016 back to Month 1 and beyond. Is it possible to make May 2016 and beyond cumulative, so that would be Month 13 and so on?

Kind regards

Adam

8

Hi @adam,

Sure give this a go.
Please note that it will return a blank for all dates prior to 2016.

Assigned number Cum = 
VAR myMonth = MONTH(Sales[Part 1 Out])
VAR myYear = YEAR( Sales[Part 1 Out] )
RETURN

IF( myYear >= 2016,
    IF( myMonth >= 5, myMonth-4,  12-(4-myMonth)) +
    12 * (myYear - 2016 )
)
9

Thanks Melissa,

I’m not sure having blanks for dates prior to 2016 would very well, as got dates in 2015.

Further would this measure continue to count through 2017, 2018 and 2019?

Thanks Adam

10

Hi @adam,

Let me emphasize that this is not a Measure but a Calculated Column added to your Sales table.
This will return a cumulative number from the first year in the data

Assigned number Cum from first date = 
VAR myMonth = MONTH(Sales[Part 1 Out])
VAR firstYear = YEAR( MINX( ALL( Sales[Part 1 Out] ), [Part 1 Out] ))
VAR myYear = YEAR( Sales[Part 1 Out] )
RETURN

IF( myYear >= firstYear,
    IF( myMonth >= 5, myMonth-4,  12-(4-myMonth)) +
    12 * (myYear - firstYear )
)

I hope this is helpful

11

Thanks Melissa,

I will give that a try.

Many Thanks

Adam

12

Hi Melissa,

Thanks for your help last week on the calculated column, that counts through my projects per month.

I would really like to explain and the formula provided last week, if we can.

For some context, there are Two Procurement Frameworks in the in the Table you were helping on. Specifically Column Name “Generation” shows numbers 3 and 4 respectively.

These two Frameworks run for the following duration’s:

  • Generation 3 runs from 15/May/2015 to the 15/May/2019, Totalling 48 Months.

  • Generation 4 runs from 15/May/2019 to the 15/May 2023, Totalling 48 Months.

  • The total of both Framework Generations being 96 months.

At present the cumulative column isn’t taking the generations 3 & 4 into account when completing the calculation. Is it possible to account for this variation in the code?

Any help on this would much appreciated.

Kind regards

Adam

13

Hi @adam,

Please review forum guidelines, your requirement keeps changing and it’s now unclear what the desired outcome should be…

Can you confirm that you want the custom numbering to restart each May 15th when a new generation begins? If not please provide more context.

Thanks

14

.

Looking at your data, a new Generation isn’t assigned on May 15th like you’ve described.
This code now creates a cycle of 48 months in each Generation (starting May 1st).

image
image877×856 50.9 KB

If you need further assistance please create a new topic and provide all necessary details.