Thursday, February 27, 2020

Power BI - Calculate Measure When Blank Value

I created 3 ways to calculate these measures

//First calculate the SUM
.ValorAPOSum = SUM('TABLE'[Valor])

//After SUM, First way
.ValorAPO1_Sum = DIVIDE(CALCULATE([.ValorAPOSum], DATESINPERIOD(Calendario[Date], LASTDATE(Calendario[Date])-1, -7, DAY)), 7,0)
.ValorAPO1 = if(ISBLANK([.ValorAPOSum]),[.ValorAPO1_Sum],[.ValorAPOSum])
.ValorAPO1_All = SUMX(Calendario,[.ValorAPO1])

//After SUM, Second way
.ValorAPOSumMoving = CALCULATE([.ValorAPOSum],DATESINPERIOD(Calendario[Date],LASTDATE(Calendario[Date]),-7,DAY))
.ValorAPO2_Sum = [.ValorAPOSumMoving]/CALCULATE(DISTINCTCOUNT(Calendario[Date]),DATESINPERIOD(Calendario[Date],LASTDATE(Calendario[Date]),-7,DAY))
.ValorAPO2 = if(ISBLANK([.ValorAPOSum]),[.ValorAPO2_Sum],[.ValorAPOSum])
.ValorAPO2_All = SUMX(Calendario,[.ValorAPO2])

//After SUM, Third way
.ValorAPO3_AVG =
VAR LAST_DATE = LASTDATE(Calendario[Date])
Var Sum_7 = calculate([.ValorAPOSum],DATESBETWEEN(Calendario[Date],DATEADD(LAST_DATE, -6, DAY),LAST_DATE))
Var Sum_all_7 = calculate([Valor APO],DATESBETWEEN(Calendario[Date],DATEADD(LAST_DATE, -6, DAY),LAST_DATE))
//return divide(Sum_7 ,Sum_all_7)
return divide(Sum_7,7)
.ValorAPO3 = if(ISBLANK([.ValorAPOSum]),[.ValorAPO3_AVG],[.ValorAPOSum])
.ValorAPO3_All = SUMX(Calendario,[.ValorAPO3])


//Count Table relations .QtdCountry2 = COUNTROWS(
FILTER(DIM_Continente_Locations,
CONTAINS(FAT_Corona3,FAT_Corona3[Country_Other],DIM_Continente_Locations[Country_Name.1])))

No comments:

Post a Comment

Power BI APP - Delete or Unpublish

 UNPUBLISH DELETE