How to create DAX to get value and end of each quarter

[laurent_rio]

Hi i made visual table of active headcount and atrition like this in Power BI 

For table 2 i still have issues since i want the table to populate the active headcount on each quarter correctly 

for example the correct value :

2020-Q1 = 11641 ( Value at March 2020)

2020- Q2 = 11389 (Value at Jun 2020)

2020-Q3 =  11229 (Value at Sep 2020)

2020-Q4 = 11289 (Value at Dec 2020)

all the year,month and quarter i use additional table date that i connect to master file 

Can help what DAX i need to use to get value at end of each quarter ?

Thanks,

[Anonymous · Accepted solution]

Hi  @laurent_rio ,

I created some data:

Here are the steps you can follow：

1. Create calculated column.

Year-Quater =
FORMAT('Table'[date],"yyyy \Qq")

Mod =
var _mod=MOD(MONTH([date]),3)
return
IF(_mod=0,1,0)

Result:

2. Place [Mod] in Filter, set is=1, apply filter.

3. Result:

Best Regards,

Liu Yang

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

[amitchandak]

@laurent_rio , This video I converted Qtr into date

https://youtu.be/yPQ9UV37LOU?t=200

And then use closingbalancequarter to use that number across that Qtr.

I have also used date table