I need help for DAX query Static to dynamic

[Anonymous]

Hello Team,

I Created below DAX function for Projects count purpose, its working fine only under but I need Dynamic view. present working in a static view.

In my present dashboard have a total of 9 filters, below DAX not reflecting those filters. 

Projects =

var a=MONTH( SELECTEDVALUE('Compliance Raw Data'[Month & Year]))-1

var c= FORMAT(max('Compliance Raw Data'[today]),"DD")

var d = FORMAT(max('Compliance Raw Data'[today]),"YYYY")

var e =CONVERT( CONCATENATE(c&"-",CONCATENATE(a&"-",d)),DATETIME)

return

CALCULATE(DISTINCTCOUNT('Compliance Raw Data'[Project ID]),ALL('Compliance Raw Data'),'Compliance Raw Data'[Project End Date]>=e,'Compliance Raw Data'[iManage Project Status]="A")

Sample raw data

I need dynamic change for the above mentioned DAX function.

[Pragati11 · Accepted solution]

Hi @Anonymous ,

Modify your dax as follows:

Projects =

var a=MONTH( SELECTEDVALUE('Compliance Raw Data'[Month & Year]))-1

var c= FORMAT(max('Compliance Raw Data'[today]),"DD")

var d = FORMAT(max('Compliance Raw Data'[today]),"YYYY")

var e =CONVERT( CONCATENATE(c&"-",CONCATENATE(a&"-",d)),DATETIME)

return

CALCULATE(DISTINCTCOUNT('Compliance Raw Data'[Project ID]),ALLSELECTED('Compliance Raw Data'),'Compliance Raw Data'[Project End Date]>=e,'Compliance Raw Data'[iManage Project Status]="A")

Thanks,

Pragati

[Anonymous]

Hi Pragati,

Somewhat it is reflecting now, but when selected last historical month, not reflecting on last month 

Month "March" not reflecting

Is there any best way to optimize the DAX function. Please help me 

[Anonymous]

Somewhat it is reflecting now, but when selected last historical month, not reflecting on last month "March" Data

[Pragati11]

Hi @Anonymous ,

What is the error on the card visual? Click on "See Details"

Thanks,

Pragati