Forum Discussion
Measure should stay fixed
Hey,
I have a Measure which calculates the sales of the current month and it
is related to a Date table and another table.
But if I click on some other charts or slicers, it will also change the calculated value
based on the selected Date.
For the other Columns and Measures - its perfect, but not for a value
that should stay fixed.
I mean, it is stupid to have a Measure that should show the current sales, but
changes it's value if you click on another Date.
Can I make it fix, so it ALWAYS shows the current month data, even if the user
clicks on other dates?
Sales = calculate(SUM('Kryal: Sales(2016-2018)'[Absatz]);FILTER('BP Kryal: Sales(2016-2018)';(Month('Kryal: Sales(2016-2018)'[Date])+Year('Kryal: Sales(2016-2018)'[Date])-(Month(Today())+Year(Today())))=0);ALL('Kryal: Sales(2016-2018)'))I know the code above isn't beautiful, but I tried a lot to make a fixed meausure and this is my current try.
Thanks,
Jnis
Hello,
just for readability I added some line breaks, but it is still awfully complicated to read. I just added colors to separate the different parts. Colors don't have any further meaning.
Sales =
calculate(
SUM('Kryal: Sales(2016-2018)'[Absatz]);
FILTER('BP Kryal: Sales(2016-2018)';
(Month('Kryal: Sales(2016-2018)'[Date])+Year('Kryal: Sales(2016-2018)'[Date])-(Month(Today())+Year(Today())))=0);
ALL('Kryal: Sales(2016-2018)'))It is hard to tell without knowing the structure of your raw data. First of all I recommend a separate DateTable but it is a matter of taste.
For your current Month this should be fine:
ActualMonth_Absatz= VAR ActualMonth = MONTH(TODAY()), VAR ActualYEAR = YEAR(TODAY()) RETURN CALCULATE( SUM(Table[Absatz]), ALL([Dates]), YEAR([Dates])=ActualYear, MONTH([Dates])=ActualMonth)
2 Replies
FloriankxSolution Sage
Hello,
just for readability I added some line breaks, but it is still awfully complicated to read. I just added colors to separate the different parts. Colors don't have any further meaning.
Sales =
calculate(
SUM('Kryal: Sales(2016-2018)'[Absatz]);
FILTER('BP Kryal: Sales(2016-2018)';
(Month('Kryal: Sales(2016-2018)'[Date])+Year('Kryal: Sales(2016-2018)'[Date])-(Month(Today())+Year(Today())))=0);
ALL('Kryal: Sales(2016-2018)'))It is hard to tell without knowing the structure of your raw data. First of all I recommend a separate DateTable but it is a matter of taste.
For your current Month this should be fine:
ActualMonth_Absatz= VAR ActualMonth = MONTH(TODAY()), VAR ActualYEAR = YEAR(TODAY()) RETURN CALCULATE( SUM(Table[Absatz]), ALL([Dates]), YEAR([Dates])=ActualYear, MONTH([Dates])=ActualMonth)
Yeah sorry, I tried a lot to fix my problem - but
your solution worked, thanks! I didnt knew before that variables remain constant :/
