Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Jnis
Frequent Visitor

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

 

1 ACCEPTED SOLUTION
Floriankx
Solution Sage
Solution 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)

View solution in original post

2 REPLIES 2
Floriankx
Solution Sage
Solution 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 😕

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.