topic Re: DAX Year to Date, Current Month, Matrix in DAX Commands and Tips

DAX Year to Date, Current Month, Matrix

spandy34 — Tue, 22 Feb 2022 14:37:32 GMT

I have a table where I have 3 measures as row headers but I would like to to have three coumns labelled , Current Month, Year to Date and Total which will filter the data. Can anyone help with the coding as to how I can do this please tamerj1 goncalogeraldes - Thanks in advance

Re: DAX Year to Date, Current Month, Matrix

goncalogeraldes — Tue, 22 Feb 2022 15:56:59 GMT

Hello there spandy34 ! I will assume that you want these values to be dynamic, thats is to say, that they depend on the selected date. So, for the current month, you can do one of the following:

Current Month = /* This one has a "dynamic current month", so that the current month corresponds to your selection*/ CALCULATE ( SUM ( 'Table'[Sales] ), FILTER ( 'DatesTable', 'DatesTable'[Month] = SELECTEDVALUE ( 'DatesTable'[Month] ) ) )

For the Year to date, you can do:

Year to Date = /* This measures uses the [Total sales] measure */ TOTALYTD ( [Total sales], 'DateTable'[DateKey] )

For the total, just use a regular SUM:

Total sales = SUM('Table'[Sales])

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes

Re: DAX Year to Date, Current Month, Matrix

spandy34 — Tue, 22 Feb 2022 18:01:40 GMT

Hi goncalogeraldes

Here is what I have put in the measure

Current Month =

CALCULATE (

SUM ( 'Main Claim Data'[ClaimRef] ),

FILTER (

'Fin_Calendar',

'Fin_Calendar'[CalMonth] = SELECTEDVALUE ( 'Fin_Calendar'[CalMonth] )))

and Year to Date

Year to Date =

--/* This measures uses the [Total sales] measure */

TOTALYTD ( [No of Claims], 'Fin_Calendar'[CalYear] )

Here are the relationships

and i get this error

Re: DAX Year to Date, Current Month, Matrix

goncalogeraldes — Tue, 22 Feb 2022 18:19:29 GMT

You need to use a column of type DATE in all time intelligence calculations. I think in your case would be:

Year to Date = TOTALYTD ( [No of Claims], 'Fin_Calendar'[DateId] --this is the field that needs to be of type date )

Make sure that the field is formatted like so:

Re: DAX Year to Date, Current Month, Matrix

spandy34 — Tue, 22 Feb 2022 18:38:46 GMT

Thats great about the Year to Date but the Current Month is getting the following error

Here is the code

Current Month =

--/* This one has a "dynamic current month", so that the current month corresponds to your selection*/

CALCULATE (

SUM ( 'Main Claim Data'[ClaimRef] ),

FILTER (

'Fin_Calendar',

'Fin_Calendar'[CalMonth] = SELECTEDVALUE ( 'Fin_Calendar'[CalMonth] )))

Re: DAX Year to Date, Current Month, Matrix

goncalogeraldes — Wed, 23 Feb 2022 16:15:07 GMT

spandy34 I think that you want to do is:

Current Month = CALCULATE ( COUNTA ( 'Main Claim Data'[ClaimRef] ), FILTER ( 'Fin_Calendar', 'Fin_Calendar'[CalMonth] = SELECTEDVALUE ( 'Fin_Calendar'[CalMonth] )))

Re: DAX Year to Date, Current Month, Matrix

spandy34 — Wed, 23 Feb 2022 16:46:16 GMT

Hi goncalogeraldes thank you for this.

I think I need this but with the ClassofBusiness = OT and Policy Code containing REC

Do you think I am approaching this the correct way? Should I be greating all these measure for the table below?

Re: DAX Year to Date, Current Month, Matrix

goncalogeraldes — Wed, 23 Feb 2022 18:02:59 GMT

spandy34 You can use MarkLaf suggestion in the other post and adapt it to this case! 🙂