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])))

Thursday, February 6, 2020

Power BI - Creating Tax measure between two tables



Table_Moviments:

Table_Tax
____________________________________________________________________

.00YYYYMMDD = MAX(Table_Moviments[a_YYYYMMDD])
____________________________________________________________________

.01GetTax = 
Var v_Val01 = "Variavel01"
return
CALCULATE(SUM(Table_Tax[ValorHora]),
          FILTER(Table_Tax,
          Table_Tax[Referencia] = v_Val01 && 
          Table_Tax[DataInicial] <= [.00YYYYMMDD]&& 
          Table_Tax[DataFinal] >= [.00YYYYMMDD]
          )
        )

____________________________________________________________________

.02GetTaxGroup = SUMX(
                SUMMARIZE(
                    Table_Moviments,
                    Table_Moviments[a_TicketPrio],
                    Table_Moviments[a_TicketDate]),
                    [.01GetTax]
)
____________________________________________________________________

.03QtdHoursRealGroup = SUMX(
                SUMMARIZE(
                    FATTickets_WorkLogs, 
                    FATTickets_WorkLogs[a_TicketPrio],
                    FATTickets_WorkLogs[a_TicketDate]),
                    [HoursMov])
____________________________________________________________________

.04VlrCostRealGroup = SUMX(
                SUMMARIZE(
                    Table_Moviments,
                    Table_Moviments[a_TicketPrio],
                    Table_Moviments[a_TicketDate]),
                    ([.03QtdHoursRealGroup]*[.02GetTaxGroup])
)
___________________________________________________________________

Power BI APP - Delete or Unpublish

 UNPUBLISH DELETE