Case When in Power BI

[StevoNorf]

Hi

I have a SQL Case When query that calculates the numbers of working days for the current month and working month total from a calendar table.

Select WorkCalendarDate.CalendarID Calendar,
Case When Month(WorkCalendarDate.TransDate) = Month(@YesterdayDateTime) And Year(WorkCalendarDate.TransDate) = Year(@YesterdayDateTime) And WorkCalendarDate.TransDate <= @YesterdayDateTime Then Abs(Sum(WorkCalendarDate.WorkTimeControl-1)) Else 0 End CMTotal,
Case When Month(WorkCalendarDate.TransDate) = Month(@YesterdayDateTime) And Year(WorkCalendarDate.TransDate) = Year(@YesterdayDateTime) Then Abs(Sum(WorkCalendarDate.WorkTimeControl-1)) Else 0 End WMTotal

I have tried to replicate this in Power BI with some DAX which works fine for all days except when the current date is the first day of a new month. I need this to work using yesterdays date details to ensure the calculation for total work days uses the previous month and not the new month where it resets the values.

CMTotalWKDays =
CALCULATE (
COUNT(WorkCalendarDate[WorkTimeControl] ),
FILTER (
WorkCalendarDate,
WorkCalendarDate[TransDate] >= CustInvoiceTrans[Start of Month]
&& WorkCalendarDate[TransDate] <= CustInvoiceTrans[Yesterday]
)
)

----------------------------------------------Total Closed Days Per Month To Date--------------------------------------------
CMTotalClosedDays =
CALCULATE (
SUM(WorkCalendarDate[WorkTimeControl] ),
FILTER (
WorkCalendarDate,
WorkCalendarDate[TransDate] >= CustInvoiceTrans[Start of Month]
&& WorkCalendarDate[TransDate] <= CustInvoiceTrans[Yesterday]
)
)

--------------------------------------------Total Work Days Current Month to Date--------------------------------------------
CMTotal = (
[CMTotalWKDays]-[CMTotalClosedDays]
)

Any ideas where I am going wrong? Any help is greatly appreciated.

[v-yueyunzh-msft · Accepted solution]

Hi, @StevoNorf 

According to your description, you want to count the number of days in a working day, right, your measure returns at the beginning of the month, you can try using the EOMONTH() function to get the date data you want.

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[StevoNorf]

Hi @v-yueyunzh-msft 

Thanks for your reply and answer. This did work for me using the EOMONTH() function that gave me the data I needed.

Thanks for your help!

Best Regards

Steve