Forum Discussion
Helper VPOWERBI : TARGET ON MATRIX
Good day!
I just want to ask if its possible, I have problem sample here:
MATRIX OUTPUT I WANT TO ACHIEVE
I want to show the changes of target depends on filter months. Is it possible or is there other solution or other way ?
I just update my sample data so that you can see , and how it's going.
Thanks!
10 Replies
- amitchandak
Super User
RanHo , You can create a measure like ,
measure =
var _max = Maxx(allselected(Table), Table[Date])
return
if(isfiltered(Table[Date]), calculate(sum(Table[Target]), filter(Table, eomonth(Table[Date],0) = eomonth(_max,0))) ,sum(Table[Target]))Make sure you create a date using month
- RanHo
amitchandak I will try it, but what if the Date/Month column is in seperate table?
- RanHo
What if the column month filter is in other Table not in the Target table?
- RanHo
What if the column month filter is in other Table not in the Target table?
- RanHo
amitchandak I just update my sample data, I can't do the formula you gave me coz the data/ sample I gave is incomplete, hope it helps.
v-henryk-mstfCommunity Support
Hi RanHo ,
In your description, which form of Date/Month is in the seperate table? By filtering Date/Month to find the corresponding sum value in the target table?
Looking forward to your reply.
Best Regards,
Henry- RanHo
v-henryk-mstf I have a table wherein the dates column located, my dates column the I want to use for filter it was separate from Target table , the MONTH column I have in Target table is just month no dates.
- amitchandak
RanHo , Create a date column using year and month in target table
like
date = "01-" &[Month] & "-" & [year] // mark that as date column
date = datevalue("01-" &[Month] & "-" & [year])
you can create a date tbale with Month , year etc and join to both tables and use that
example
Addcolumns(calendar(date(2020,01,01), date(2021,12,31) ), "Month" , month([date]) , "Year", year([date]), "Month Year", format([date],"mmm-yyyy") , "Month year sort", year([date])*100 + month([date]))
Join with both tables
and then use that as filter
Part of this blog can help
Distributing/Allocating the Monthly Target(Convert to Daily Target): Measure ( Daily/MTD): https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Monthly-Target-Convert-to/ba-p/1657798
- RanHo
I just update my sample data, hope it can help understand. By the way the Date column in Production table will be the filter.
