Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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)
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 😕
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
80 | |
67 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |