Forum Discussion
How to store the values or While loop.
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.
1 Reply
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
