how to calculate pervious 6 month when date column in a Matrix

[MikeCheng]

Hi all

how to calculate pervious 6 month when date column is text format?

here is the sample data link:

https://docs.google.com/spreadsheets/d/13WJvIqLFjyzkP9cEK4QychWAwjGcio5m/edit?usp=sharing&ouid=11255...

I have these 2 calculations:

previous 6 month sum =

CALCULATE (

    SUM('Raw data'[acture amount]),

    DATESINPERIOD ( 'Raw data'[年月].[Date], MAX ( 'Raw data'[年月].[Date] ), -6, MONTH )

)

actural amount x180 divided by pervious 6 month sum = DIVIDE([acture amountx180], [previous 6 month sum])

when I use date hierarchy, the numbers are correct but it become 3 layers Matrix, it is too hight

But when I don't use the date hierarchy, the high of the Matrix is correct, but the numbers are wrong

please help me build a Matrix which has only 2 layer with yyyy-mm format and the correct number

sample data link is provided

Best regards,

Mike

[v-veshwara-msft]

Hi @MikeCheng ,

Just checking in to see if you query is resolved and if any responses were helpful.
Otherwise, feel free to reach out for further assistance.

Thank you.

[v-veshwara-msft]

Hi @MikeCheng ,

Thanks for reaching out to Microsoft Fabric Community.

Also, thanks @kushanNa  for your inputs and suggestions.

@MikeCheng , just wanted to check if the solution provided has addressed your needs and if you have considered the benefits of creating a Date table as mentioned by @kushanNa .

As mentioned, using a separate Date table is not mandatory, but it helps simplify calculations like YTD, QTD, and moving averages. This ensures your measures return correct results even when the column in your Matrix is in yyyy-mm format, keeping the Matrix at two layers.

Please reach out for further assistance.

Thank you.

[kushanNa · Accepted solution]

Hi @MikeCheng 

are you looking for an output similar to this ? 

if that what you are looking for i have attached the pbix file for your reference 

[MikeCheng]

yes, this is what I am Looking for, can you explain why to create another date table?

[MikeCheng]

Hi  kushanNa

yes, this is what I looking for, can you explain why does it need to create another date table?

[kushanNa]

Hi @MikeCheng 

I believe it’s not always a must to create a Date table, but I usually create one when troubleshooting these types of scenarios since it makes things less complicated. Also, if you plan to do more time intelligence (YTD, QTD, moving averages, etc.), having a Date table will save you a lot of headaches.