cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

starting count (12 months ago)

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

1 ACCEPTED SOLUTION
Super User

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.

4 REPLIES 4
Community Support

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.

Super User

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.

Helper II

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.

Super User

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.

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.