The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
This sort of problem appears over and over again online, but I can't find anything that answers my question, so apologies if this repeats anything posted previously.
We have a large report that is being driven by a date table. The date table, helpfully named 'DateTable' is a dax table. It has been marked as a date table. By 'driven' I mean that all other tables have a relationship with it and it will form the basis for almost all interaction via a dropdown, and is used in other calculations.
So, I have a monthly sum dataset. This dataset is just a simple table which calculates a total for the month from another table. So, when added to a table visual, it basically looks like this:
What I need to be able to do is have a column on a table visual that shows the same period the previous year. I created a measure to do this, which looks like this:
I know I can use the 'SamePeriodLastYear' function, but it'll be used to go back further, so I subtracted a year instead. It does what I would expect and my table looks like this:
You can see that the new column displays 77 for 30/04/2024 because the previous period end, 30/04/2023, was 77. And there is nothing for 30/04/2017 because the data doesn't go any further back.
But, I need the DateTable table to be used, so I created a new measure by copying the previous one and replacing the references, and it looks like this:
But it doesn't work. When I add it to the table visual it just displays the same years' data but omits the most recent:
I've found countless guides and examples online that describe the same basic issue, but none that display the same result.
What am I doing wrong and how stupid am I being?
Solved! Go to Solution.
@HotChilli Thanks for your contribution on this thread.
Hi @Mat42 ,
What's your expected result? Do you want to get the values for the previous year and apply the date field of DateTable on the visual to replace the field [Period TA] of table? I created a sample pbix file(see the attachment), please check if that is what you want.
Best Regards
'Period End' in the visual - is that from the date table or the Fact table?
Period End is from the fact table. It's a basic table of totals for a month.
So, we have a table that details figures for each day of the month, then this table is just a total of those figures. The dates in the table are all the last days of each month, with a total for that month. That first image is exactly how the fact table looks:
There are no 'in between' dates, it's just the last day of each month for multiple years. In the example it's the years 2017-2024 for March and April.
@HotChilli Thanks for your contribution on this thread.
Hi @Mat42 ,
What's your expected result? Do you want to get the values for the previous year and apply the date field of DateTable on the visual to replace the field [Period TA] of table? I created a sample pbix file(see the attachment), please check if that is what you want.
Best Regards
User | Count |
---|---|
86 | |
84 | |
36 | |
34 | |
34 |
User | Count |
---|---|
94 | |
79 | |
65 | |
55 | |
52 |