topic Re: two related table Year and month in DAX Commands and Tips

two related table Year and month

RedaBi — Thu, 04 Jul 2024 21:41:52 GMT

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?

Re: two related table Year and month

aduguid — Thu, 04 Jul 2024 22:53:26 GMT

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

Re: two related table Year and month

Anonymous — Fri, 05 Jul 2024 05:17:22 GMT

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.

Re: two related table Year and month

RedaBi — Fri, 05 Jul 2024 12:44:18 GMT

Excellent idea, I really like this tip

Re: two related table Year and month

RedaBi — Fri, 05 Jul 2024 12:46:49 GMT

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