Create a measure (using virtual tables) that shows...

Anonymous · ‎03-15-2023

Hello, everybody.

This is my first time posting here, so I just apologise beforehand if I'm doing anything wrong here. I just need your help.

My problem is that I want to create two measures (current remaining area and current weighted waiting time) that shows me the value of the differentiation between two different tables that include the monthly harvest and sowing plan for each site of the company, respectively. The remaining area is get from the difference between the cummulative harvest area to date and the cummulative sowed area to date ("to date" means up to the last day of the selected month in the slicer) and the weighted time is supposed to be calculated with the time gap between the latest harvest and the earliest sowing dates for each plot, weighted by the remaining area of each lot in the month selected.

Actually, I've already solved the problem of the remaining area, it's just the weighted time gap that gives me trouble. My idea to solve this was to create a virtual table that joints the two tables (Harvest Plan and Sow Plan) summarised by Plot_ID and do the calculations as if they were a normal table, just with the latest_date column taking a dynamic value according to the slicer selection.

Here are two reference tables for each:

Harvest plan:

Sow plan

The main goal is to create two visuals, one that displays the evolution of the remaining area per site at any given month, and the other is to show what is the average waiting time of that site (weighted by plot size). The first visual is already done, and here is the code I used to do it:

Delta_area_dynamic =
VAR _endmonth = MAX('Calendar'[Date])
 VAR _dynamic_delta = CALCULATE(SUM(Plan_Cosecha_2023[Harvested Area (Ha)])-SUM(Plan_Siembra_2023[Transplanted Area (Ha)]),FILTER(ALL('Calendar'),'Calendar'[Date]<=MAX('Calendar'[Date])))
return IF(_dynamic_delta>0.1,ROUND(_dynamic_delta,2),BLANK())

To the time gap I made two measures, with the former working the closest to what I want (but not quite, as it doesn't take into consideration the evolution of the remaining area). The problem with both shows when I have a site that is sowed in different months. The values for the time gap are not properly weighted.

Delta_month_dynamic_delay_ = 

VAR tabla_unfil = CALCULATETABLE(Plan_Cosecha_2023,FILTER(ALL('Calendar'),'Calendar'[Date]<=MAX('Calendar'[Date])))

VAR _diff_date = ADDCOLUMNS(tabla_unfil,"_Delta_time_",DATEDIFF([Harvest_date],MAX('Calendar'[Date]),DAY))

VAR _diff_weighted = CALCULATE(SUMX(_diff_date,DIVIDE([_Delta_time_]*[Harvested Area (Ha)],SUM([Harvested Area (Ha)]))),FILTER(ALL('Calendar'),'Calendar'[Date]<=MAX('Calendar'[Date])))

VAR _Harv_plant_dynamic_delay =  IF([Delta_area_dynamic]>0,DIVIDE(_diff_weighted,30))

VAR tabla_unfil_ = CALCULATETABLE(Plan_Siembra_2023,FILTER(ALL('Calendar'),'Calendar'[Date]

VAR _tabla_max = ADDCOLUMNS(tabla_unfil_,"Max_date",IF(MAX('Calendar'[Date])>[Transplanting_date],[Transplanting_date],MAX('Calendar'[Date])))

VAR _diff_date = ADDCOLUMNS(_tabla_max,"_Delta_time_",DATEDIFF([Harvest_date],[Max_date],DAY))

VAR _diff_weighted = CALCULATE(SUMX(_diff_date,DIVIDE([_Delta_time_]*[Transplanted Area (Ha)],SUM([Transplanted Area (Ha)]))),ALL('Calendar'),'Calendar'[Date]<=MAX('Calendar'[Date]))

return IF(AND([Delta_area_dynamic]>0,_diff_weighted>0),DIVIDE(_diff_weighted,30),[_Harv_plant_dynamic_delay])

The second one is just a hot mess.

Delta_time_dynamic = 

VAR MAXX_fecha = MAX('Calendar'[Date])

VAR cosecha_unfil = CALCULATETABLE(SUMMARIZE(Plan_Cosecha_2023,Plan_Cosecha_2023[Concat_lote],"Hda",LOOKUPVALUE(Plan_Cosecha_2023[Site],Plan_Cosecha_2023[Concat_lote],Plan_Cosecha_2023[Concat_lote]),"Are_cumm",SUM(Plan_Cosecha_2023[Harvested Area (Ha)]),"Harvest_date",MAX(Plan_Cosecha_2023[Harvest_date])),FILTER(ALL('Calendar'),'Calendar'[Date]<=MAX('Calendar'[Date])))

VAR acumul_siembra_ = CALCULATE(SUM(Plan_Siembra_2023[Transplanted Area (Ha)]),FILTER(ALL('Calendar'),'Calendar'[Date]<=MAX('Calendar'[Date])))

var latest_siembra_ = IF(MAX(Plan_Siembra_2023[Transplanting_date])>MAXX_fecha,MAXX_fecha,MAX(Plan_Siembra_2023[Transplanting_date]))

VAR joint_unfil = ADDCOLUMNS(cosecha_unfil,"Transp_ha",acumul_siembra_,"Latest_date",latest_siembra_)

VAR delta_table = ADDCOLUMNS(joint_unfil,"Delta_ha",([Are_cumm]-[Transp_ha]),"Delta_time",DIVIDE(DATEDIFF([Harvest_date],[Latest_date],DAY),30))

VAR delta_pond = ADDCOLUMNS(delta_table,"Pond_time_delta",SUMX(delta_table,DIVIDE([Delta_time]*[Delta_ha],SUM([Delta_ha]))))

VAR total_time_pond = SUMMARIZE(delta_pond,[Pond_time_delta])

return IF(AND([Delta_area_dynamic]>0,total_time_pond>0),total_time_pond,[_Harv_plant_dynamic_delay])

I just don't know what else I can do, apart from creating an ad-hoc table that filter the desired value for each month, but that is going to be nuts to elaborate for each site.