Showing the Year amount in a graph (column and line) for every month the same year amount

[rpinxt]

This is the data I bring in :

the number 1944 I want to show in all periods of 2022.

I made this measure :

Now for easier vieuwing I put what I want in a table:

So these are the periods (2022 and 2023) and are on the x-axis of a column/line chart.

With colums with other amounts etc.

Now I want to bring a line in with the amount 1944 on all the periods and in 2023 the amount is 1920.

But as soon as I bring in measure UCL this happens:

It only puts in 12-2022 because the source only had 1 period. It was only the year I made it 12-xxxx everywhere hoping that would help.

So is there a way with a measure that for every period of a year it shows the same amount?

[Ritaf1983 · Accepted solution]

Hi  @rpinxt
You can use Dax measure like :
profit_all_year =
CALCULATE (
    [your measure] ),
    ALL ( your_table[Month_year] ),
    YEAR ( 'your_table'[Date] )
        = CONVERT ( RIGHT ( ( SELECTEDVALUE ( 'your_table'[Month_year] ) ), 4 ), INTEGER )
)

Pbix with example is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[Ritaf1983 · Accepted solution]

Hello, @rpinxt!

I'd be happy to help. The DAX logic can be a bit challenging to explain completely, but I'll do my best.

The key idea is that there are two filters involved: one to be ignored and another to be applied.

Imagine DAX as a set of instructions for a robot. In the first step, I instructed the 'robot' to completely disregard the period filter, which can be translated as 'all' in its language.

After that, I needed the 'robot' to focus on extracting the year from the column, so I added the second filter condition.

I hope this explanation helps clarify things for you 😊

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[Ritaf1983 · Accepted solution]

Hi  @rpinxt
You can use Dax measure like :
profit_all_year =
CALCULATE (
    [your measure] ),
    ALL ( your_table[Month_year] ),
    YEAR ( 'your_table'[Date] )
        = CONVERT ( RIGHT ( ( SELECTEDVALUE ( 'your_table'[Month_year] ) ), 4 ), INTEGER )
)

Pbix with example is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[rpinxt]

Hi @Ritaf1983 ,

As said this solution worked beautiful.

Now however the uses added something new.

On a country level there could be month that there where no complaints.

The visuals filters these months out but user wants to see them.

No problem to switch them on, however this disturbs this solution apparently:

Is there a workaround for this?

[rpinxt]

Well.....this is excellent....😲 @Ritaf1983 

Really works also perfectly in the column/line chart.

Getting a sort of "trend" line horizontal that we needed.

Very pleased.

Althought I have no idea why this is working.

Because I already tried with the second filter, ALL, but that did not work (my first filter was ShipGrp must be Complaint Groups).

So guessing all the magic happens in the third filter with year and converting it somehow.

Would you be able to explain to my why this works?

[Ritaf1983 · Accepted solution]

Hello, @rpinxt!

I'd be happy to help. The DAX logic can be a bit challenging to explain completely, but I'll do my best.

The key idea is that there are two filters involved: one to be ignored and another to be applied.

Imagine DAX as a set of instructions for a robot. In the first step, I instructed the 'robot' to completely disregard the period filter, which can be translated as 'all' in its language.

After that, I needed the 'robot' to focus on extracting the year from the column, so I added the second filter condition.

I hope this explanation helps clarify things for you 😊

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[rpinxt]

Ok I think that made sense for me.

So first you tell to disregard the period and then you tell to filter the amount per year?
And therefore it gives on all the periods the year amount?

[Ritaf1983 · Accepted solution]

Yep.