Forum Discussion
Need help : Visualization and filtering
- Anonymous7 years ago
Since there is no data about groups in the target table, we cannot directly filter target based on groups. There are some different ways to allocate the target over groups ( i'd highly recommend this page on how that can be accomplished https://www.daxpatterns.com/budget-patterns/ )
But if here's what I came up with in the meantime:
As you can see, there is the target for 3/1/2016 for each group (because group id cannot reach target). so an idea would be to take the amount of each group and divide the total target, but that's just an idea.
Here's the DAX for the above measure:
Total Target Checking = //Calculates the Last Date in the Target Table Var __LastDate = CALCULATE( LASTDATE(FactSalesTarget[TargetDate]), Filter( ALL ( DimCalendar[Date]), LASTDATE(DimCalendar[Date]))) Return //Checks to see if the LastDate is the same date in the current filter context Var __LastDate_Equals_Current_Date = __LastDate = Max(DimCalendar[Date]) Return /*Checks to see if the Last date is greate than the date in the current filter context if it is, then also check to see if the Total Sales are Not blank*/ Var __EarlierDate_NotBlankSales = AND( __LastDate > Max( DimCalendar[Date]), NOT( ISBLANK( [Total Sales Amount])) ) Return /* Final fucntion, if either of the above are true, then give the total target, if not, then give nothing */ IF ( OR( __LastDate_Equals_Current_Date, __EarlierDate_NotBlankSales ), [Total Target] )
Since there is no data about groups in the target table, we cannot directly filter target based on groups. There are some different ways to allocate the target over groups ( i'd highly recommend this page on how that can be accomplished https://www.daxpatterns.com/budget-patterns/ )
But if here's what I came up with in the meantime:
As you can see, there is the target for 3/1/2016 for each group (because group id cannot reach target). so an idea would be to take the amount of each group and divide the total target, but that's just an idea.
Here's the DAX for the above measure:
Total Target Checking =
//Calculates the Last Date in the Target Table
Var __LastDate =
CALCULATE(
LASTDATE(FactSalesTarget[TargetDate]),
Filter(
ALL ( DimCalendar[Date]),
LASTDATE(DimCalendar[Date])))
Return
//Checks to see if the LastDate is the same date in the current filter context
Var __LastDate_Equals_Current_Date =
__LastDate = Max(DimCalendar[Date])
Return
/*Checks to see if the Last date is greate than the date in the current filter context
if it is, then also check to see if the Total Sales are Not blank*/
Var __EarlierDate_NotBlankSales =
AND(
__LastDate > Max( DimCalendar[Date]),
NOT( ISBLANK( [Total Sales Amount]))
)
Return
/* Final fucntion, if either of the above are true, then give the total target,
if not, then give nothing */
IF (
OR(
__LastDate_Equals_Current_Date,
__EarlierDate_NotBlankSales
),
[Total Target]
)Hi Nick,
Thanks!
Really appreciated your guidance, it help me a lot.
Btw, also thanks for the link, it's very cool and interesting to learn more from there.
:smileyvery-happy: