Forum Discussion
How To Set Target(Monthly) through Dax
Good day Guys, i just want to ask how can I set target using dax, like that yellow bar on my sample photo. That was my Target for the year 2021, what if I'm going to add different value target for year 2022?
(target = 2465) - this was my static Dax , but I need to add more Target for different years so that whenever I filter different year it will change depends on filter, how can I do that?
Thanks in Advance!
Hi RanHo ,
According to your description, you want to dynamically set the Target based on the different year.
If so, please try:
target = SWITCH(MAX('Table'[Date].[Year]),2019, 51108, 2020,3528,2021,2465,2022,5673)If you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
RanHo , On of the way is create a target table using enter data , You can give target based on year end date. and join with your date table and use it
refer my blog
How to Enter Data and Edit it: https://youtu.be/5nE7YGT72kU
You do not want to allocate, but option is there, refer my blog
Distributing/Allocating the Yearly Target(Convert to Daily Target): Measure ( Daily/YTD): Magic of CLOSINGBALANCEYEAR With TOTALYTD/DATESYTD: https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Yearly-Target-Convert-to/ba-p/1476400
amitchandak Sir, is there a way using Dax? like switch or anything like that if possible?
RanHo ,
A static measure , that you can change as per need
Target = 2000
A DAX Table
Target = row("Date", Date(year(today()) , 12,31), "Target", 2000)
or
Target = row("Date", Date(year(today()) , 12,31), "Year",year(today), "Target", 2000)
Hi, RanHo
Create a table that contain target per year & month and then connect it to your calendar table.
Date Target 01-Jan-21 2465 01-Feb-21 2465 01-Mar-21 2465 01-Apr-21 2465 01-May-21 2465 01-Jun-21 2465 01-Jul-21 2465 01-Aug-21 2465 01-Sep-21 2465 01-Oct-21 2465 01-Nov-21 2465 01-Dec-21 2465 01-Jan-22 3000 01-Feb-22 3000 01-Mar-22 3000 01-Apr-22 3000 01-May-22 3000 01-Jun-22 3000 01-Jul-22 3000 01-Aug-22 3000 01-Sep-22 3000 01-Oct-22 3000 01-Nov-22 3000 01-Dec-22 3000
Hi RanHo ,
According to your description, you want to dynamically set the Target based on the different year.
If so, please try:
target = SWITCH(MAX('Table'[Date].[Year]),2019, 51108, 2020,3528,2021,2465,2022,5673)If you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
