DAX Query Not working

[homelander123]

PMPCCountNew =

Var SelectedDate = SELECTEDVALUE(Query1[CSMDate])

Var PrevMonthStart = EOMONTH(SelectedDate, -2) +1

Var PrevMonthEnd = EOMONTH(SelectedDate,-1)

RETURN

IF( NOT ISBLANK(SelectedDate),

CALCULATE(

    SUM(Query1[PCCount]),

    Query1[ICMDate] >= PrevMonthStart &&

    Query1[ICMDate] <= PrevMonthEnd

    )

)

I Want to calculate the count however the issue is that my slicer is based out of CSMDate and i want to calculate For ICMdate. This query isnt giving me any results. Can you please help?

[v-venuppu]

Hi @homelander123 ,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please accept it as a solution and give it a 'Kudos' so other community members with similar problems can find a solution faster.

Thank you.

[v-venuppu]

Hi @homelander123 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-venuppu]

Hi @homelander123 ,

Thank you @maruthisp for the Prompt Response.

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you

[maruthisp · Accepted solution]

Hi @homelander123 ,

Please find the attached pbix file for your solution.
What it does below:

1. use ALLEXCEPT(Query1,Query1[CSMDate]) inside CALCULATE so you keep the slicer’s filter but drop everything else
2. Measure - dynamically computes the prior-month window and sums PCCount across it

CSMDate and ICM Date.pbix

Please let me know if you have further questions.

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

[v-venuppu]

Hi @homelander123 ,

Thank you for reaching out to Microsoft Fabric Community.

It addresses the key issue: the slicer is on CSMDate, but the aggregation must happen over ICMDate.

You can try to Calculate Previous Month PCCount using CSMDate Slicer:

PMPCCount =
VAR SelectedDate = MAX(Query1[CSMDate])
VAR PrevMonthStart = EOMONTH(SelectedDate, -2) + 1
VAR PrevMonthEnd = EOMONTH(SelectedDate, -1)
RETURN
CALCULATE(
SUM(Query1[PCCount]),
FILTER(
ALL(Query1),
Query1[ICMDate] >= PrevMonthStart &&
Query1[ICMDate] <= PrevMonthEnd
)
)

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

[homelander123]

PMPCCount =

VAR SelectedMonth = MAX(Query1[CSMDate])

RETURN

CALCULATE(

    SUM(Query1[PCCount]),

    Query1[ICMDate] >= EOMONTH(SelectedMonth, -2) + 1 && Query1[ICMDate] <= EOMONTH(SelectedMonth, -1)

)

I also tried this  but it didnt work