How to store the values or While loop.

[Anonymous]

HI All...

I have Date and employee code columns. i need to store the count of the employees for last 12 months for each month. 

EX:

Last 12 months	logic	From	to
Aug-23	From Aug-23 to last 12 months counts need store under Aug-23	Sep-22	Aug-23
Jul-23	From Jul-23 to last 12 months counts need store under jul-23	Aug-22	Jul-23
Jun-23	From Jun-23 to last 12 months counts need store under Jun-23	Jul-22	Jun-23
May-23	From May-23 to last 12 months counts need store under May-23	Jun-22	May-23
Apr-23	From Apr-23 to last 12 months counts need store under Apr-23	Mar-22	Apr-23
Mar-23	From Mar-23 to last 12 months counts need store under Mar-23	Apr-22	Mar-23
Feb-23	From Feb-23 to last 12 months counts need store under FEB-23	Mar-22	Feb-23
Jan-23	From Jan-23 to last 12 months counts need store under Jan-23	Feb-22	Jan-23
Dec-22	From Dec-22 to last 12 months counts need store under Dec-22	Jan-22	Dec-22
Nov-22	From Nov-22 to last 12 months counts need store under Nov-22	Dec-21	Nov-22
Oct-22	From Oct-22 to last 12 months counts need store under Oct-22	Nov-21	Oct-22
Sep-22	From Sep-22 to last 12 months counts need store under Sep-232	Oct-21	Sep-22

please see the below screen. i need to show three columns employees count for the last 12 month with aboce logic.

[rubayatyasmin]

Hi, @Anonymous 

Create a Date table first then create a relationship with the date tables date column and employee tables date column. Then use DATESINPERIOD() dax func. 

For instance, 

CALCULATE( COUNT('Employees'[employee_code]), DATESINPERIOD('DateTable'[Date], MAX('DateTable'[Date]), -12, MONTH) )

refer: https://learn.microsoft.com/en-us/dax/datesinperiod-function-dax

or you can use DATESBETWEEN(). 

refer: https://radacad.com/datesinperiod-vs-datesbetween-dax-time-intelligence-for-power-bi