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.
Hello:
I have 2 measures:
12M M³ Tratados = is the moving sum of the last 12 months
YoY% de 12 VT = is the yearly change in % of the previously described variable. For the last available month (January 2023) the calculation is ((64,157,367 / 60,751,835) -1) * 100 = 5.606%.
The problem:
I want to put a card on my report that shows the YoY% de 12 VT for the last month available, January in this case. But the card showed is the followed:
This number is the same that is showed at the bottom of the previous table and is the variation of 12M M³ Tratados against the value showed in December 2022: ((64,157,367 / 64,141,072) -1) * 100 = 0.0254%.
When I filter my report by dates, this card works fine, showing this calculation for each month, but the problem arise when no date is selected.
How can I show the by default the calculation for January 2023 (last month available)?
Here you can see how the measures are built:
Thank for your help
Hi @Greyesbud ,
You can create a measure as below to replace the measure [YoY% de 12M VT] onto the visuals:
Measure =
VAR _maxfdate =
CALCULATE ( MAX ( 'Table'[Date] ), ALL ( 'Table' ) )
VAR _mindate =
DATE ( YEAR ( _maxfdate ) - 1, 1, 1 )
VAR _maxdate =
DATE ( YEAR ( _maxfdate ) - 1, 12, 31 )
VAR _CYVT =
CALCULATE (
[VT],
DATESBETWEEN (
'Calendario'[Fecha].[Date],
DATE ( YEAR ( _maxfdate ), 1, 1 ),
_maxfdate
)
)
VAR _PYVT =
CALCULATE (
[VT],
DATESBETWEEN ( 'Calendario'[Fecha].[Date], _mindate, _maxdate )
)
RETURN
IF (
ISFILTERED ( 'Calendario'[Fecha].[Date] ),
[YoY% de 12M VT],
DIVIDE ( ( _CYVT - _PYVT ), _PYVT )
)
If the above ones can't help you, could you please provide some raw data in your table (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:
How to provide sample data in the Power BI Forum
How to Get Your Question Answered Quickly
And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
Hi yingyinr
Many thanks for your help.
Unfortunately, this measure doesn't work. I need the card to change the number when you select a month.
I found a solution replacing inside the original measure (YoY% de 12M VT), the measure that calculates the sum of the last 12 months ([12M VT]) by its content:
This new measure is the following:
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 |
---|---|
56 | |
27 | |
24 | |
14 | |
9 |
User | Count |
---|---|
77 | |
61 | |
47 | |
17 | |
12 |