Shift Months -1 in a Date Matrix

[Anonymous]

Hi all,

Have been battling on this one yesterday and couldn't get what I needed with GPT. It could be the data needs restructuring but was hoping it'd be possible using DAX.

I have an issue where I've got a date matrix (End Date along the X and Created Date along the Y). The End Date is a simple Date dim of how we phase revenue, the Created Date is when that revenue was created, so you there will be revenue created in March, for the month of March, Apr, May, Jun and July.

What I'm trying to do is shift my data so Feb (let's call this M0) has data for Feb at End and Feb at Created in Cell 1, then Cell 2 under Feb End Date, it has Mar at End Date, but Mar at Created Date. I've attached some screen shots below. Hopefully I've explained that well enough but please say if more context is needed.

The desired result is to be able to take a 6 month average of the M0 column, then the M1 column, and so on.

Exisiting format

Desired format

[Anonymous]

Ah, think I've figured it out 🙂 Thank you so much!

[Anonymous]

Hi Aniya,

Thanks so much for getting back to me, I appreciate it's not a simple request!

The desired output is definitely correct, but I think there might be a couple of tweak needed. I didn't mention the three different tables I'm using here (my bad). Firstly, the End Date field is in a Time Period table, the Created Date is in a Proposal table and the Revenue figure is in the Finance table. Does this mean I'll need to make some changes in Step 1 in order to create the measure and column?

Thanks,

Jake

[v-yueyunzh-msft]

Hi , @Anonymous 

Sure , if the data sturcture and context filter is different , it may modify the step 1 dax code , so if you can not modify the dax code in your side , you can give me your sample data with differnt tables and give me the end result as a table format . (You can give me Excel file or pbix file without sensitive data!)  And you need also tell me how you calculate the value in the first image1.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[v-yueyunzh-msft · Accepted solution]

Hi ,  @Anonymous 

According to your description,Are you trying to shift your values to the left in units of rows?

This is my understand for your need:

If this , here are the steps you can refer to :
(1)We can add two calculated columns in your table :

Row2 = FORMAT([Row],"mmmm")

Column2 = FORMAT([Column],"mmmm")

(2)We need to create a Date table like this and we do not need to create any relationship between them:

Date = ADDCOLUMNS( CALENDAR(DATE(2023,1,1), DATE(2023,12,31) ) , "MonthName" ,  FORMAT([Date],"mmmm") , "Month" ,  MONTH([Date]))

(3)Then we can create three measures:

Measure = SUM('Table'[Vaue])

 

Measure 2 = var _t  =ADDCOLUMNS( CROSSJOIN(ALLSELECTED('Table'[Row2]) ,ALLSELECTED('Table'[Column2])) ,"v" ,

 CALCULATE([Measure], ALLEXCEPT('Table','Table'[Row2],'Table'[Column2],'Table'[Row].[Month],'Table'[Column].[Month]))  )

var _row =  MAX('Date'[MonthName])

var _column = MAX('Table'[Column])

var _blank_value =COUNTROWS( FILTER(_t ,[Row2] = _row&& [v] = BLANK( )))

var _change_column  = EOMONTH(_column , _blank_value )

var _value = SUMX(FILTER(_t  ,[Row2]  = _row && [Column2] =FORMAT(_change_column,"mmmm") ) , [v])

return

_value

 

 

Measure 3 =

 var _column_total = SUMX( ADDCOLUMNS( VALUES('Date'[MonthName]) , "v" , [Measure 2] ) ,[v])

 var _v =  IF( HASONEVALUE('Date'[MonthName]) ,[Measure 2],_column_total)

 var _v2 =  CALCULATE([Measure] , 'Table'[Row2] in VALUES('Date'[MonthName]))

 return

IF( NOT( HASONEVALUE('Table'[Column2]) )  ,_v2  , IF(NOT( HASONEVALUE('Date'[MonthName]) ) ,_column_total ,[Measure 2]  ))

 

 

Then we can put this fields on the visual and we can get this Matrix in the visual:
 

As i have no your sample data and i am not surely if you need this result . If this can not help you solve the problem,

you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly