Forum Discussion
Measure calculation with Matrix (Dates)
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.
3 Replies
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.
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.
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.
