Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mweel111
New Member

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

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.

1 REPLY 1
lbendlin
Super User
Super User

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-...

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/1447...

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.