IF function with variants

Hi t-atgeor,

Based on my understanding, you want to get the summarize table of cost and sales which you choose, right?

If it is a case, you can refer to below steps: (use union function and measure to create a dynamic table visual)

Data tables:
Cost.                                                     
 

Sales.

DateTable = VALUES(Sales[Fiscal Month])

Add a measure to get selected date:

Selected Date = IF(HASONEVALUE(DateTable[Fiscal Month]),VALUES(DateTable[Fiscal Month]),BLANK())

Use union function to create a table with two records:

Table = UNION(ROW("Metric","Sales"),ROW("Metric","Costs"))

Add measures to calculate current value, previous value, current quarter, previous quarter, total.

The type of record:

Curr Type = LASTNONBLANK('Table'[Metric],'Table'[Metric])

Current month value:

Curr value = if([Curr Type]="Costs",
LOOKUPVALUE(Cost[Costs USD],Cost[Fiscal Month],[Selected Date]),
LOOKUPVALUE(Sales[Sales USD],Sales[Fiscal Month],[Selected Date]))

Previous month value:

Prev value = 
var currtype= LASTNONBLANK('Table'[Metric],'Table'[Metric])
var temp=DATE(YEAR([Selected Date]),MONTH([Selected Date])-1,DAY([Selected Date]))
return
if([Curr Type]="Costs",
if(LOOKUPVALUE(Cost[Costs USD],Cost[Fiscal Month],temp)<> BLANK(),
LOOKUPVALUE(Cost[Costs USD],Cost[Fiscal Month],temp),0),
if(LOOKUPVALUE(Sales[Sales USD],Sales[Fiscal Month],temp)<>BLANK(),
LOOKUPVALUE(Sales[Sales USD],Sales[Fiscal Month],temp),0 ))

Current quarter value:

Curr Quarter = 
var quarter = SWITCH(MONTH([Selected Date]),1,1,2,1,3,1,4,2,5,2,6,2,7,3,8,3,9,3,10,4,11,4,12,4)
var minMonth =(quarter-1) *3 +1
var maxMonth=quarter* 3
return
if([Curr Type]="Costs",
SUMX(FILTER(ALL(Cost),
AND(MONTH(Cost[Fiscal Month])>=minMonth,MONTH(Cost[Fiscal Month])<=maxMonth)&&YEAR(Cost[Fiscal Month])=YEAR([Selected Date])),
Cost[Costs USD]),
SUMX(FILTER(ALL(Sales),
AND(MONTH(Sales[Fiscal Month])>=minMonth,MONTH(Sales[Fiscal Month])<=maxMonth)&&YEAR(Sales[Fiscal Month])=YEAR([Selected Date])),
Sales[Sales USD]))

previous quarter value:

Prev Quarter = 
var quarter = SWITCH(MONTH([Selected Date]),1,1,2,1,3,1,4,2,5,2,6,2,7,3,8,3,9,3,10,4,11,4,12,4)
var minMonth =(quarter-2)*3 +1
var maxMonth=(quarter-1)*3
var currYear= YEAR([Selected Date])
return
if(quarter<>1,
if([Curr Type]="Costs",
SUMX(FILTER(ALL(Cost),
AND(MONTH(Cost[Fiscal Month])>=minMonth,MONTH(Cost[Fiscal Month])<=maxMonth)&&YEAR(Cost[Fiscal Month])=YEAR([Selected Date])),
Cost[Costs USD]),
SUMX(FILTER(ALL(Sales),
AND(MONTH(Sales[Fiscal Month])>=minMonth,MONTH(Sales[Fiscal Month])<=maxMonth)&&YEAR(Sales[Fiscal Month])=YEAR([Selected Date])),
Sales[Sales USD])),
if([Curr Type]="Costs",
SUMX(FILTER(ALL(Cost),
AND(Cost[Fiscal Month]>=DATE(currYear-1,10,1),Cost[Fiscal Month]<=DATE(currYear-1,12,31))),
Cost[Costs USD]),
SUMX(FILTER(ALL(Sales),
AND(Sales[Fiscal Month]>=DATE(currYear-1,10,1),Sales[Fiscal Month]<=DATE(currYear-1,12,31))),
Sales[Sales USD])))

Total = [Curr Quarter]+[Prev Quarter]

Create visuals to display the result.
Slicer:

Table visual:

Notice: if you didn’t select a date or select multiple dates, the table visual will get an error.

Regards,
Xiaoxin Sheng