It is linked to the date table below:
The date is the first column and I’ve added the first day of the month on the last column. I’ve also added the name of the month, like “Jan-18”.
I have a measure that SUMs the amount in the fact table which is the field “Mont”.
I’ve done another measure that calculates a 12 month running total of this measure, which is:
Both measures work fine as shown below, when I put the date as the row header:
However, if I change the row to the name of the month, which is also in the date table, it doesn’t work any more:
What I can’t understand is why Power BI cannot calculate based on the date field in the background, and show the result with the name of month header, as both fields are in the date table.
I’m not too bothered about this model, but I feel I’m missing some key logic in DAX.
It’s not ideal, but you could choose to add the full date column to the report, and the Month/Year column, then reduce the column size of the date column to hide it.
As you can see in the above shot, the Date column is still on the table, I just resized the column width to hide it.
But, this is a ‘hacky’ solution - so you need to remember to always:
turn of the Auto-Size Column Width (Column Headers)
turn off Word Wrap (Column Headers)
turn off Word Wrap (Values)
optional change the font color for the column you are hiding in case an end-user manages to do something you don’t expect
Hi @Pbovet, did the response provided by @AntrikshSharma and @Heather help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!
Hi @Pbovet, we’ve noticed that no response has been received from you since the 13th of February. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!
Hi @Pbovet, did the response provided by @AntrikshSharma and @Heather help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!
Hi @Pbovet, we’ve noticed that no response has been received from you since the 16th of February. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
Hi @Pbovet, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.
