Create 12 cards that dynamically change to the previous 12 months.

[Anonymous]

Hello,

In my query, I grab data with dynamic dating and have to label each column as "month 12", "month 11", "month 10", etc.

Once in my powerBI matrix, the column names remain as they are in my query. I do not want to manually change them every single month for this report, so I had the idea of having cards above each column that would dynamically change with each month. Then when I reload, it will be "September 2022", "October 2022", "November 2022", etc.

What would be the best way to go about this process? Should I create a separate table with 12 measures to calculate  current month -1, current month -2, etc? What would that syntax look like? Should I create 12 measures in my current table?


Table currently looks like this, for reference:

[parry2k · Accepted solution]

@Anonymous You are missing a part for not using PQ. Anyhow, first, the structure of your data is not ideal, it is preferred to have unpivoted data instead of pivoted data. In your case, you don't need to change anything on the SQL side (if you don't want to), and then unpivot the data in the PQ, and extract the month number which can also be easily done in PQ. Again, if this can be done in the backend, nothing like that which is always a preference.

Once you have an unpivoted table with the month number (through PQ or SQL), add another table in the model using DAX, see attached solution. I called this Month for Header and then set the relationship of this table with the transaction table on the month number.

Now you can easily have month name as a matrix header, this will work based on the ReportingPackage[Placement_YearMonth] which I think is used to find out the most recent transaction to calculate the month range.

PS: in the attached I try to mimic the data you have from your backend and then transform it in PQ.

[Anonymous]

Got it to work! I split off a second table from the main table that will be used for this waterfall matrix only, and then pivoted the necessary columns like you said. This pivot creates a few million rows, but it looks good and works as intended and the data matches my reference. So far so good! Now I need to wait for the month to roll over to ensure that everything works with the reference time frame shifting. I appreciate your help and patience with me. This has been a huge help.

[parry2k]

@Anonymous you are using PQ to pull the data though, I assume you are using import mode?

[Anonymous]

I guess so. I just have a connection to MS-SQL server for my 3 tables that live there. Connected through "Get Data" and have done all other modifications using DAX for measures and columns. I rarely ever use the Power Query Editor.

[parry2k]

@Anonymous It is very easy to solve both in DAX and PQ especially if there are always 12 months, starting from the last month from the current date and rolling back 12 months. It is super easy to rename these dynamically in PQ and then show the actual month name on the matrix header rather than the way you are doing now.

[Anonymous]

How would I do that in DAX?

I have a connection set up to MS SQL server tables, which are updated  via scheduled stored procedure, so I wasnt using PQ at all and was hoping to avoid it if I could. But if I could do this in DAX I'd be over the moon. 

[parry2k]

@Anonymous yes it will but I was thinking more of doing it in a way to not have these calculations and Card visuals to layover on the matrix visual, which will do the job but is not ideal, and will be hard to manage.

[Anonymous]

I asked the same question before on this forum if there was a way to dynamically rename or change column headers. No one could give me a way to do it. I'll show a screenshot of my data as well, but the matrix headers I want to be dynamic are also my connect query result table headers. I dont know of a way to have these headers adjust monthly with the data reload.

Current data table of applicable column names:

Current best effort for matrix:

I can rename these "Month12_Patient_NetbackCollections" to just Month 12, but I dont know of any way to dynamically have them rename themselves to the accurate months. SQL column names cannot be dynamic either, so I have to keep them static in my query.

[parry2k]

@Anonymous how raw data look like not how you see it in matrix visual?

[Anonymous]

I actually just figured it out with a combo of:

Month-1 = max(ReportingPackage[Placement_YearMonth])

Month-2 = EDATE([Regression_Month-1],-1)

Month-3 = EDATE([Regression_Month-1],-2)

I think this will auto update everything per monthly load so that month 1 will grab the latest month in the set and the rest of the months will stair-step backwards by 1 month increments using the EDATE.

[parry2k]

@Anonymous how do you know month 12 is what month? Is there a date column associated with each month?

[Anonymous]

Month 12 will be current month -13.

Month 1 will be current month -1.