two related table Year and month

[RedaBi]

Hello, I have two tables (Calendar and Sales). The sales table contains only the month names and the years. I need to calculate the sales according to the year and the month. I have created two relationships: one by year (active) and one by month (inactive). I then created the following measure:

CALCULATE(SUM(Sim_Prelevement[Qte]),Sim_Prelevement[Year] = SELECTEDVALUE(Calendrier[Year]),USERELATIONSHIP(Calendrier[Month],Sim_Prelevement[month_texte]))​

The measure works very well when I select one year in a slicer, but when I select multiple years, the sum of sales by year and month does not display. Is there a measure that can do the job differently?

[Anonymous · Accepted solution]

Hi @aduguid ,Thanks for the quick reply, I'll add further.

Hi @aduguid ,

Regarding your question, the 'SELECTEDVALUE' function only returns the value if there is only one non-duplicate value. If you select more than one year, the null value is returned.

SELECTEDVALUE function - DAX | Microsoft Learn

Try this.

CALCULATE(SUM(Sim_Prelevement[Qte]),Sim_Prelevement[Year] IN VALUES('Calendrier'[Year]),USERELATIONSHIP(Calendrier[Month],Sim_Prelevement[month_texte]))

If there are still problems, please provide the .pbix culture without sensitive data. Or provide simple data and present the expected results as a picture.

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi @aduguid ,Thanks for the quick reply, I'll add further.

Hi @aduguid ,

Regarding your question, the 'SELECTEDVALUE' function only returns the value if there is only one non-duplicate value. If you select more than one year, the null value is returned.

SELECTEDVALUE function - DAX | Microsoft Learn

Try this.

CALCULATE(SUM(Sim_Prelevement[Qte]),Sim_Prelevement[Year] IN VALUES('Calendrier'[Year]),USERELATIONSHIP(Calendrier[Month],Sim_Prelevement[month_texte]))

If there are still problems, please provide the .pbix culture without sensitive data. Or provide simple data and present the expected results as a picture.

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RedaBi]

Indeed, it worked. I knew I had to replace SELECTEDVALUE, but I didn't know with what. Thank you, this information will help me with my future measures

[aduguid]

I would create a calculated column and join it to your calendar table.

MonthDate = 
VAR _month_nbr = 
    SWITCH(
        TRUE(),
        Sim_Prelevement[month_texte] = "January", 1,
        Sim_Prelevement[month_texte] = "February", 2,
        Sim_Prelevement[month_texte] = "March", 3,
        Sim_Prelevement[month_texte] = "April", 4,
        Sim_Prelevement[month_texte] = "May", 5,
        Sim_Prelevement[month_texte] = "June", 6,
        Sim_Prelevement[month_texte] = "July", 7,
        Sim_Prelevement[month_texte] = "August", 8,
        Sim_Prelevement[month_texte] = "September", 9,
        Sim_Prelevement[month_texte] = "October", 10,
        Sim_Prelevement[month_texte] = "November", 11,
        Sim_Prelevement[month_texte] = "December", 12,
        BLANK()  -- or any other default value you prefer
    )
VAR _result = DATE(Sim_Prelevement[Year], _month_nbr, 1)

RETURN
_result

[RedaBi · Accepted solution]

Excellent idea, I really like this tip