topic Re: Measure calculation with Matrix (Dates) in DAX Commands and Tips

Measure calculation with Matrix (Dates)

Anonymous — Thu, 01 Apr 2021 08:56:31 GMT

I´m having troubles using measures in matriz, and i hope that someone can help me.

So my problem is the next:

I want to calculate the sum of all where the date = date of piece, being that the date is the line of the matriz. I have tried to calculate with the code:

Calculate(Sum(profit), Filter(table, table[dataPiece].[Year] = max(table[data].[Year] && table[dataPiece].[Year] = max(table[data].[Month])))

But the result ins´t getting right because some times the date ins´t the same that the date of piece.

Re: Measure calculation with Matrix (Dates)

amitchandak — Thu, 01 Apr 2021 09:06:53 GMT

Anonymous , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Are you trying to get same month data everywhere

The try

Full month = CALCULATE(SUM(Table[Profit]),DATESMTD(ENDOFMONTH('Date'[Date])))

month till date

MTD = CALCULATE(SUM(Table[Profit]),DATESMTD('Date'[Date]))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Re: Measure calculation with Matrix (Dates)

Anonymous — Thu, 01 Apr 2021 09:54:02 GMT

amitchandak ,For exemple:

I have created an column "DateFull" that gets where de piece current is situated:

And my matrix:

And my Measure:

As we can see, the result in January is getting 1, but the result expected is 5 because all 5 is situated in January.

Re: Measure calculation with Matrix (Dates)

Anonymous — Thu, 01 Apr 2021 12:51:41 GMT

Hi Anonymous

I would strongly advise that you read this guide on how to create correct models in PBI before you start creating something at work. I can see you are using automatically generated date hierarchies in your table(s). This is totally wrong. You should never do it. Please create a dedicated date dimension table in the model. The guide will explain why you should not do that and have a dedicated date/time dimension.