Dax Query Help / End of Year and / End Of Year/Month

mweel111 — Fri, 17 May 2024 22:15:07 GMT

I have this query and my DAX query works fine. If they slice the cube by date all the correc totals come up.

CALCULATE(

SUM(PatientBalanceAging[PatientPortion]),

FILTER(

'PatientBalanceAging',

INT( CONVERT( MAX( 'Date'[Year] ), STRING ) & "1231" ) =

'PatientBalanceAging'[Date]

)

However I am trying to create an additional query where if they slice by year and month (to look at total across year and month) this query yields no results

CALCULATE(

SUM(PatientBalanceAging[PatientPortion]),

FILTER(

'PatientBalanceAging',

INT(LEFT(PatientBalanceAging[Date], 4)) = MAX('Date'[Year]) &&

INT(MID(PatientBalanceAging[Date], 5, 2)) = MAX('Date'[Month]) &&

INT(RIGHT(PatientBalanceAging[Date], 2)) = 31

)

My date column is an integer as YYYYMMDD. This query is trying to get snap shots of accounts receivables that are posted each day. So if they look by month it gets the last day of the month in the dataset.

Thank you in advance for any help.

Re: Dax Query Help / End of Year and / End Of Year/Month

lbendlin — Sat, 18 May 2024 19:41:17 GMT

not all months have 31 days?

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

topic Dax Query Help / End of Year and / End Of Year/Month in DAX Commands and Tips

Dax Query Help / End of Year and / End Of Year/Month

Re: Dax Query Help / End of Year and / End Of Year/Month