DAX Year to Date, Current Month, Matrix

[spandy34]

Hi 

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

[spandy34]

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

[goncalogeraldes · Accepted solution]

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:

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

[spandy34]

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] )))

[goncalogeraldes]

@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] )))

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

[spandy34]

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?

[goncalogeraldes]

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

[goncalogeraldes]

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