Visual POWER BI : display only until the desired period

[Anonymous]

Hi,

Here is my problem and I hope you will have the answer. I have this visual

At the bottom left I have a histogram with all the periods. However I have filtered on 202104. I would like to have only the value until 202104 without having the value of 202105. I use only one table for this graph. It's the same as the filter.

I have a formul the determinate the REEL_MOIS with one filter : 

REEL_MOIS = cALCULATE(sum('Contrôle budgetaire'[Valeur]),filter(all('Contrôle budgetaire'[Attribut]),'Contrôle budgetaire'[Attribut]="A"))

I try to add a condition if the period is superior to my selection so I do nothing otherwise I do the calcul but It doesn't work...

(a table of multiple values ​​was provided while a single value is expected)

REEL_MOIS = if(filter(all('Contrôle budgetaire'[ANNEE-MOIS]),'Contrôle budgetaire'[ANNEE-MOIS]<=SELECTEDVALUE('Contrôle budgetaire'[ANNEE-MOIS])),cALCULATE(sum('Contrôle budgetaire'[Valeur]),filter(all('Contrôle budgetaire'[Attribut]),'Contrôle budgetaire'[Attribut]="A")),0)

Do you have any idea?

Thanks in advance for your help

[PaulDBrown · Accepted solution]

Ok, to recap. The slicer must be from the disconnected period table; the period field in the visual must be from the original 'Contrôle budgetaire'[ANNEE-MOIS]

[Anonymous]

Sorry, I didn't undersand before. It's clear and it's OK 

I've a better comprehension of this measure and join.

Thank you for your help

[PaulDBrown]

Try:

New REEL_MOIS = if(MAX('Contrôle budgetaire'[ANNEE-MOIS])<=SELECTEDVALUE('Contrôle budgetaire'[ANNEE-MOIS]),cALCULATE(sum('Contrôle budgetaire'[Valeur]),filter(all('Contrôle budgetaire'[Attribut]),'Contrôle budgetaire'[Attribut]="A"))).

you probably need this same structure for the other measure in the visual

[Anonymous]

Hi PaulDBrown,

It's certainly something like that I want but there is no change

The formul seems clear but I haven't enough experience to understand why it doesn't work

[PaulDBrown]

What table(s) are the fields from the visual and the slicer from?

[Anonymous]

PaulDBrown,

It's the table 'Controle budgetaire' for slicer and the visual. I saved and open the power bi and with your formul I've this result :

There is only the period filtered. 

[PaulDBrown]

The table you are using as a slicer must  be disconnected in the model. 

[Anonymous]

Ok, so what I've done is correct? to have the result with the 4 periods

[PaulDBrown]

Change the measure to:

New REEL_MOIS = if(MAX('Contrôle budgetaire'[ANNEE-MOIS])<=SELECTEDVALUE(' disconnected table Contrôle budgetaire'[ANNEE-MOIS]),cALCULATE(sum('Contrôle budgetaire'[Valeur]),filter('Contrôle budgetaire', 'Contrôle budgetaire'[Attribut]="A"))).

the field in the visual must then be from the original Contrôle budgetaire'

[Anonymous]

I've change the measure and now all the period have the result of the month 04

Thank you again for your quick feedback and your help

[Anonymous]

I've create an other table (without join with 'Controle budgetaire') and I have the period I want. Now the problem is that I've the same value (value of the mont 04) on all the period

[parry2k]

@Anonymous try this measure

REEL_MOIS = cALCULATE(sum('Contrôle budgetaire'[Valeur]),keepfilters('Contrôle budgetaire'[Attribut]="A"))

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

Hi parry2k,

I've made your formul but there is no change

But thank you for your quick feedback and make me discover the function "KEEPFILTERS"

[Anonymous]

Please use allexcept condition in your measure,

REEL_MOIS = cALCULATE(sum('Contrôle budgetaire'[Valeur]),allexcept('Contrôle budgetaire','Contrôle budgetaire'[Attribut]))

[Anonymous]

Hi Rajashri_Viz,

I try your formul but unfortunately I've an other problem

I notice that all my data is in one table. 

'Contrôle budgetaire'[Valeur]====> the column with value I want to sum

'Contrôle budgetaire'[Attribut]===> the column with criteria "A" for Actual ( because I've "Bud A" for Budget)

'Contrôle budgetaire'[ANNEE-MOIS]===> the column with the period (same column used for the filter 

Thank you all for helping me

[Anonymous]

Please refer to my edit in my previous post, allexcept(table name, table[column)

Also you might have to check edit interactions to assure you have your histogram and your slicer is interacting or not.

Hope this helps @Anonymous 

Thanks

[Anonymous]

I try to give you all the information.

*I have 2 tables without join

*The slicer has one field [ANNEE-MOIS] and comes from the table 'Contrôle budgetaire'. It is interacting with the histogram

* Currently the histogram has the period from the table 'Periode CB' and the value from the table 'Contrôle budgetaire'

The measure is : 

New REEL_MOIS = if(max('Contrôle budgetaire'[ANNEE-MOIS])>=SELECTEDVALUE('Période CB'[ANNEE-MOIS]),CALCULATE(sum('Contrôle budgetaire'[Valeur]),filter(all('Contrôle budgetaire'[Attribut]),'Contrôle budgetaire'[Attribut]="A")))

but the result takes only the month 04 on each period.

I try "allexcept" but if I do this on [Attribut], the result seems takes all the values