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.
Hi everyone,
I'm seeking your help because I'm puzzled by filter and dax measure. The following screeshot is showing a formula I've written to calculate the selling amount for a specific year (2020).
Hand writing the value of 2020 produces no problem since every single customer is associated to the 2020.
But using the slicer max value produce an undesired situation: the max year is selected for each customer and not as a general value. For example a customer who was active till 2019 return the selling amount of 2019, and not the desidered 2020.
How can I get the max value from the slicer indipendently from the row on which I'm applying the measure?
Thank you!
Solved! Go to Solution.
Hi @gabrielefugazzi ,
Please refer to my .pbix file.
Hi @gabrielefugazzi ,
Please refer to my .pbix file.
Thank you Xue Ding,
I've learned an important lesson throught your help.
Hi ,
a specific year filter has been applied to the report (as shown below) which select the last ywo year from the dataset. In our case 2020 and 2019
A sample customer record with selling amount per year, from the tabular model
ID | 2020 | 2019 | TOTAL |
0201000084 |
| 8,52 | 8,52 |
Running the dax formula (where year is fixed) per last year and last year-1
fatturato_ac = VAR maxdate = 2020 return
if(isblank(round(CALCULATE(sum(fatturato[fatturato]), fatturato[anno_fattura] = maxdate), 2)), 0, round(CALCULATE(sum(fatturato[fatturato]), fatturato[anno_fattura] = maxdate), 2))
fatturato_ap = VAR maxdate = 2019 return
if(isblank(round(CALCULATE(sum(fatturato[fatturato]), fatturato[anno_fattura] = maxdate), 2)), 0, round(CALCULATE(sum(fatturato[fatturato]), fatturato[anno_fattura] = maxdate), 2))
produces the following matrix row
Running the dax formula (where year is not fixed) per last year and last year-1
fatturato_ac = VAR maxdate = max(fatturato[anno_fattura])
return
if(isblank(round(CALCULATE(sum(fatturato[fatturato]), fatturato[anno_fattura] = maxdate), 2)), 0, round(CALCULATE(sum(fatturato[fatturato]), fatturato[anno_fattura] = maxdate), 2))
fatturato_ap = VAR maxdate = max(fatturato[anno_fattura])-1
return
if(isblank(round(CALCULATE(sum(fatturato[fatturato]), fatturato[anno_fattura] = maxdate), 2)), 0, round(CALCULATE(sum(fatturato[fatturato]), fatturato[anno_fattura] = maxdate), 2))
Have you seen that the value shifts from one year to another?
@gabrielefugazzi ,The information you have provided is not making the problem clear to me. Can you please explain with an example.
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Appreciate your Kudos.
Hi @amitchandak,
a specific year filter has been applied to the report (as shown below) which select the last ywo year from the dataset. In our case 2020 and 2019
A sample customer record with selling amount per year, from the tabular model
id 2020 2019 totale
0201000084 0 8,52 8,52
Running the dax formula (where year is fixed) per last year and last year-1
produces the following matrix row
Running the dax formula (where year is not fixed) per last year and last year-1
Have you seen that the value shifts from one year to another?
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |