starting count (12 months ago)

[Maheshguptaz]

Hi,

I'm trying to get the starting count from 12 months ago and it should be dynamic to perform R12 calculations. For example:

Ending count = Oct'21
Starting Count = Nov'20

I need help to get the starting count. Any suggestions?

Regards,
Mahesh

[v-xiaotang]

Hi @Maheshguptaz 

You can use Edate() or Eomonth(),

https://docs.microsoft.com/en-us/dax/edate-function-dax

https://docs.microsoft.com/en-us/dax/eomonth-function-dax

e.g.

Start Date = 
    VAR _date= MAX('DATE Table'[Date]) //in this example, max date in table is 2021/10/21
    var _start=EDATE(_date,-12)
RETURN _start

Start Date = 
    VAR _date= MAX('DATE Table'[Date]) //in this example, max date in table is 2021/10/21
    var _start=EOMONTH(_date,-12)
RETURN _start

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[VahidDM]

Hi @Maheshguptaz 

You can use the PREVIOUSYEAR DAX function to find the solution.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!

[Maheshguptaz]

hi @VahidDM ,

I'm getting the total of last 12 months if I use the following DAX:

R12 Start number = CALCULATE(COUNTA(ExcelDB_Headcount[Employee ID]),PREVIOUSYEAR('Calendar'[Date]))

I'm trying to get only the count for a single month.

[VahidDM · Accepted solution]

HI @Maheshguptaz 

Try this:

R12 Start number =
VAR _EndDate =
    MAX ( 'Calendar'[Date] )
VAR _StartDateS =
    DATE ( YEAR ( _EndDate ) - 1, MONTH ( _EndDate ) - 1, 1 )
VAR _StartDateF =
    ENDOFMONTH ( _StartDateS )
RETURN
    CALCULATE (
        COUNTA ( ExcelDB_Headcount[Employee ID] ),
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Date] >= _StartDateS
                && 'Calendar'[Date] <= _StartDateF
        )
    )

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!