Previous Month calculation is not working correctly with multiple slicers

[Anonymous]

Hi All,

Where am I going wrong to get Previous Month workday calculation. But working for Current Month calculation.

Previous Month Workdays =

VAR a=SELECTEDVALUE('Date Dimension'[Month])

VAR b=SELECTEDVALUE('Date Dimension'[Year])

VAR c=SELECTEDVALUE('DIM_ORDER'[Order_Type])

VAR d=SELECTEDVALUE('DIM_PRODCATEGORY'[Prod_Cat])

VAR e=SELECTEDVALUE('DIM_CATEGORY'[Catageory])

VAR g=SELECTEDVALUE('DIM_CUSTOMER'[Customer_Name])

RETURN

CALCULATE(MEDIAN(Metric_WorkDays[WorkDay_Value]),

FILTER(ALLEXCEPT(Metric_WorkDays,Metric_WorkDays[Work_Days]),

Metric_WorkDays[Month No]=a-1 &&

Metric_WorkDays[Year]=b &&

Metric_WorkDays[Order_Type]=c &&

Metric_WorkDays[Prod_Cat]=d &&

Metric_WorkDays[Catageory]=e &&

Metric_WorkDays[Customer_Name]=g))

Current Month Workdays =

VAR a=SELECTEDVALUE('Metric_WorkDays'[Financial Month])

RETURN

CALCULATE(AVERAGE(Metric_WorkDays[WorkDayCount]),

      FILTER(ALLSELECTED(Metric_WorkDays),

           Metric_WorkDays[Financial Month]=a &&

           Metric_WorkDays[Year]=SELECTEDVALUE(Metric_WorkDays[Year])))

Thanks In Advance,

Tanushree

[v-xuding-msft]

Hi @Anonymous ,

Can you please share some sample data and the expected results? Then we will understand your requirement clearly.

[v-xuding-msft]

Hi @Anonymous ,

Could you tell me if your problem has been solved? Welcome to share your own solution. More people will benefit from the thread.

Best Regards,

Xue Ding

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

[Anonymous · Accepted solution]

Hi @v-xuding-msft 

I was making it complex. I was considering all my slicers in Filter() of my code.

This simple DAX works for me.

Previous Month Workdays = CALCULATE(SUM(Fact_WorkDays_TBL[Workdays]),DATEADD('Date Dimension'[Date],-1,MONTH))

Thanks

[vanessafvg]

@Anonymous  i could be wrong, think it may be how you calculate month, are you trying to go back a month, what if month 1 = 1 and going back is 12?  your calculation wont get there, you need to use time intelligence to calculate this properly.

what are you getting and can you demonstrate examples of what you want.  Are you able to use a date field?