Forum Discussion
Measure formula problem
I am writing some measures to calculate,let's say, sum of sales for past n months [n is 3,6,12,24,36]. All will be shown in cards.
I have data up until feb 2024 , starting from jan 2019
The normal measure for sum is
Sale_Sum = sum(tablename.columnname)
This one is working fine.
When i tried to make specific measure , something like this:
Sale_Sum_3_months = Calculate(Sale_Sum, filter(tablename,datecolumn <= max(table.datecolumn) && datecolumn >= max(table.datecolumn)-90)).
Again this is working fine.
Slicers made of other columns working perfectly fine with this card. But problemcomes with year slicer. In all 5 cases[ 3,6,9,12,36 months ], when i select 2024, it shows data only for jan and feb 24, whereas technically it should sum of 3 months[ dec 23, jan 24, feb 24]. Also for 24, 36 month cases when 1 year is selected, lets say 2023, then only 2023 data is shown instead of showing 2023-22 data for 24 months and 2023-22-21 for 36 months. I tried using "all(tablename)" or "all(tablename.datecolumn" function in first part of filter where tablename is present , but then rest of slicers made from other columns will not work. Only year slicer will work. If i use removefilters(tablename) instead of tablename in filter column, then other slicers will work , but date colum or year slicer will not. Any suggesstions what else can I try. I will try my best to give fast replies for solving this problem better.
It looks like date column and rest of column slicers working in different directions . One does not work with other. Any suggestion will be helpful
He tried to help you, reading theory is paramount in DAX/PBI. At minimum you need to have 2 separate tables (Sales and dates) here then only DAX will work otherwise DAX has a concept of "auto exists" it won't let your code return what you want unless you switch to star schema.
Even after that you would need a disconnected dates table because if you filter 2024 you can't just return dates of 2023, so you need a slicer that has year column from disconnected dates table that doesn't filter the visual directly, instead it is filtered based on the DAX code.
Here are 2 example of that disconnected dates table: https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Previous-6th-month-max-date-sales/m-p/3981439#M154386
(select 2017 & 2018 from filters pane and then select any month from first quarter of 2018)
https://www.youtube.com/watch?v=A0h_0kf-0oI&ab_channel=AntrikshSharma
8 Replies
- MattAllingtonCommunity Champion
Based on your description, it sounds like you have a single table. You should create a star schema. Here are some articles I wrote on the topic.
https://exceleratorbi.com.au/the-optimal-shape-for-power-pivot-data/https://exceleratorbi.com.au/dax-time-intelligence-beginners/
- Rakshhit_SharmaAdvocate I
That case is not possible. I am doing internship in a comapny. I have to manage this data in its current way.
- AntrikshSharmaCommunity Champion
Then as an intern the first thing you learn and do today is to push back on bad data model and make them adapt the star schema.