Delta with dynamic range of data

[Anonymous]

I wrote this DAX to calculate delta

but delta so calculated is not dynamic with changing dates on filter.

I have this kind of filter

If I want to display the delta between May 2024 and March 2023, for the year 2024 it will display the delta between May 2024 and May 2023, not for the range I selected in the filter (May2024 and March 2023). How can I modify the query so that the filter controls on the delta to be displayed? Thank you so much

[lucadelicio · Accepted solution]

Hi @Anonymous ,

first of all without the model is very difficult to help you.
However i'll try.
I presume that date filter is Tempo[data] and is filtering the fact table where you calculate the sell.
So when you select march2023 and may2024 your context filter of the page will change and the "venduto" measure will rapresent the sum of sell of march2023+may2024.
I suggest to create two data tables not connected with the fact table and using it as a slicer to selected the two period of comparison:
DateTable1 and DateTable2 with the same dates of Tempo[Data] BUT NOT CONNECTED IN THE MODEL WITH ANY TABLE.
So you will choose the two period to compare on DateTable1 and DateTable2 and create a measure to calculate the comparison of the two period.

Try:
Measure =
var _firstperiod =
CALCULATE(
[Venduto]
,Tempo[Data]>= MIN(DataTable1[Data]) && Tempo[Data]<= MAX(DataTable1[Data])
)
var _secondperiod =
CALCULATE(
[Venduto]
,Tempo[Data]>= MIN(DataTable2[Data]) && Tempo[Data]<= MAX(DataTable2[Data])
)
RETURN
DIVIDE(
_secondperiod - _firstperiod
,firstperiod
)
I think it could work.
More easy if you post the pbix file on a share service.
Let me know if you will be able to resolve.

Ciao!

[Anonymous]

Your solution works, but now all years, even those not selected, show up in my graph. How do I get only the selected dates to show? Thank you!

[lucadelicio]

Hi  ,
if you need to have more than one slicer in a page you have to manage the interaction of it on the visuals.So put the Tempo[Data] slicer in the page and then stop the interaction with the visual that has the comparison measure.

Format, Edit interaction - Select the slicer Tempo[Data] and then stop the interaction with the visual with the comparison measure selecting this icon on it 

Now the slicer tempo[data] will not work on this visual.
Refer to this documentation:
https://learn.microsoft.com/en-us/power-bi/create-reports/service-reports-visual-interactions?tabs=p...

Ciao

@Anonymous

[Anonymous]

I put two filters as you said, in the report, a filter to select the previous period, in which I put the Date variable (Where the year is in it (from hierarchy)) of DateTime1 and a filter to select the current period, with Date variable (Where the year is in it (from hierarchy)) of DateTime2. Then in the table I inserted the variable Year of DateTable2. And so it took out all the years except the year of the current selected period, of DateTime2. But I would also need to display the year of the previous selected period, of DateTime1.
But if I put in the Year columns of DateTime1, I get all the years in that table.

So I am no longer using the variable Time[Date] as a filter but the new variables from DateTime1 and DateTime2. Because if I use Time[Date] as a filter, I go back to the original problem.

What should I enter to make the previous and current year, selected in the filter, appear in the table?

Ciao collega! Grazie molto

[lucadelicio]

Show me an image please

[Anonymous]

done

[lucadelicio]

You talk about of other graph can i see all the page? and the problem and the interaction of all the objects of the page please? Thank you

[Anonymous]

here i choose 2022 jan and 2023 feb, and the table shows only last one. but i'd like that shows also 2022 for comparison. In "Seleziona periodo precedente" i have Anno from DataTable1 and in Seleziona Periodo Attuale i inserted Anno drom DataTable2. Delta Dinamico is your measure that works fine

[lucadelicio]

If you want to calculate the total sell and the dynamic in the same filer context (period) you need to change the measure.

In the main matrix you have to choose the year from the Tempo[Data] so the total sell will be ok automatically.
Then you have to exclude this filer context from the dynamic difference adding a ALL(Tempo[Data]) filter in the formula.
Change the two var like this, the rest o the measure remains the same:

var _firstperiod =
CALCULATE(
[Venduto]
,ALL(Tempo[Data])
,Tempo[Data]>= MIN(DataTable1[Data]) && Tempo[Data]<= MAX(DataTable1[Data])
)
var _secondperiod =
CALCULATE(
[Venduto]
,ALL(Tempo[Data])
,Tempo[Data]>= MIN(DataTable2[Data]) && Tempo[Data]<= MAX(DataTable2[Data])
)
RETURN
DIVIDE(
_secondperiod - _firstperiod
,firstperiod
)

Mark as a solution if i help you.
Thank you!

[Anonymous]

i've done this edit but now if i select from the filter using Tempo[Data] as filter, delta doesn't show anymore. Why? 
I èut Tempo[Data] on this table (The year 2022 and 2023 come from this)