topic Re: Measure formula problem in DAX Commands and Tips

Measure formula problem

Rakshhit_Sharma — Sun, 16 Jun 2024 11:35:14 GMT

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

Re: Measure formula problem

MattAllington — Sun, 16 Jun 2024 22:53:34 GMT

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/

Re: Measure formula problem

Rakshhit_Sharma — Mon, 17 Jun 2024 08:09:48 GMT

That case is not possible. I am doing internship in a comapny. I have to manage this data in its current way.

Re: Measure formula problem

AntrikshSharma — Mon, 17 Jun 2024 09:03:23 GMT

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.

Re: Measure formula problem

MattAllington — Mon, 17 Jun 2024 09:06:20 GMT

You do not need to ask for help on forums if you already know the correct answer. 🤷‍♂️

Re: Measure formula problem

Rakshhit_Sharma — Mon, 17 Jun 2024 09:50:27 GMT

I have come here, because i do not know the solution. I am stuck at a junction where one type of filter will allow one set of slicers to work and other will allow different set. I am trying to find middle ground between those 2 filters . The community has always helped me in tough times. Thanks for cooperation

Re: Measure formula problem

AntrikshSharma — Mon, 17 Jun 2024 12:16:37 GMT

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

Re: Measure formula problem

Rakshhit_Sharma — Mon, 17 Jun 2024 12:19:18 GMT

Sorry for sounding rude. I will try your suggestions and come back with more updates. Thank you for your reply. AntrikshSharma

Re: Measure formula problem

Rakshhit_Sharma — Wed, 19 Jun 2024 14:27:45 GMT

Hi AntrikshSharma MattAllington , Come here to tell u guys that the problem is solved. Congratulations. The idea of making a new dimension date table from main date table worked well. Thanks for your ideas. Hope to again meet you guys with some new query. Thanks for your guidance, once again