Dax or Calculated column

[Anonymous]

Hi All,

I'm trying to count total no of records as below:

Report Date         Raised Date      Description

1.02.2022              14.03.2022        xyhj123 

1.02.2022               02.01.2022       louyt45

1.02.2022               10.10.2021       kterty23

1.02.2022               05.01.2022       perty41

Output as bedlow:

Record Count for January 2022 = 2

I prefer measure as a solution, please note I always need a count of previous month to report date , in this example total records for Jan 2022 were 2 & report date is Feb 2022, so it should be dynamic.

Thank You in advance 

[v-angzheng-msft · Accepted solution]

Hi, @Anonymous 

Try this:

Count = 
CALCULATE(COUNT('Table'[Raised Date]),FILTER(ALL('Table'),'Table'[Raised Date]<MAX('Table'[Report Date ])&&'Table'[Raised Date]>EOMONTH(MAX('Table'[Report Date ]),-2)))

Result:

Please refer to the attachment below for details.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

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

[amitchandak]

@Anonymous , Create a date table and have active join with report date and inactive join with raised date

then you can have measure like

reported = calculate(count(Table[Description]))

Raised = calculate(count(Table[Description]), USERELATIONSHIP ( Table[Raised Date], 'DateD[Date] )
)

Then use these measure in time intelligence

MTD Raised= CALCULATE([Raised],DATESMTD('Date'[Date]))
last MTD Raised= CALCULATE([Raised],DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Raised= CALCULATE([Raised],previousmonth('Date'[Date]))

MTD reported = CALCULATE([reported ],DATESMTD('Date'[Date]))
last MTD reported = CALCULATE([reported ],DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month reported = CALCULATE([reported ],previousmonth('Date'[Date]))

refer

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA