DAX - Calculo de tempo , age, idade

 .Age = INT ( YEARFRAC ( [birthday].[Date], TODAY (), 1 ) )

===================================================================

 .Age = INT ( YEARFRAC ( [birthday].[Date], TODAY (), 1 ) )

===================================================================

.V_Dt_Encerramento =

if(TRIM([/BIC/ZDTENCERR]) = "" , "ERR Enc Null",

if(TRIM([/BIC/ZDTABER]) = "" , "Err Abt Null",

if([/BIC/ZDTENCERR] = "00000000", "ERR Enc ZERO",

if(AND(MID([/BIC/ZDTENCERR],"1","4") = "9999",MID([/BIC/ZDTABER],"1","4") = "9999"), "Err - Dup Abt & Fec",

if(AND(MID([/BIC/ZDTENCERR],"1","4") = "9999",MID([/BIC/ZDTABER],"1","4") <> "9999"), "OK - Aberto",

if(NOT OR(MID([/BIC/ZDTENCERR],"1","1") = "1",

          MID([/BIC/ZDTENCERR],"1","1") = "2"), "ERR DT",

"OK - Fechado"))))))

.qtdAnosP =

var dtA_year = INT(MID([/BIC/ZDTABER],"1","4"))

var dtA_month = INT(MID([/BIC/ZDTABER],"5","2"))

var dtA_day = (MID([/BIC/ZDTABER],"7","2"))

var dtA = DATE( (dtA_year) ,   (dtA_month) ,   (dtA_day))

RETURN

IF(DIM_SAP_Postos_New[.V_Dt_Encerramento] = "OK - Aberto",INT ( YEARFRAC ( dtA, TODAY (), 1 ) ))

DAX - Dynamic Field Parameter Grouping

 1 - Create Dynamic Table (Modeling -> New Parameters -> Fields )

Table_Measure_Gender= {

    (".Gender_M_Brasil",NAMEOF('FAT_Table'[.Gender_M_Brasil]), 0),

    (".Gender_F_Brasil",NAMEOF('FAT_Table'[.Gender_F_Brasil]), 1),

    (".Gender_M_Argentina", NAMEOF('FAT_Table'[.Gender_M_Argentina]), 2),

    (".Gender_F_Argentina", NAMEOF('FAT_Table'[.Gender_F_Argentina]), 3)

}

Column_DynamicGroup = SUBSTITUTE('Table_Measure_Gender'[Table_Measure_Gender],"M_","")

IF(SEARCH("M_",'Table_Measure_Gender'[Table_Measure_Gender],1,0),

    SUBSTITUTE('Table_Measure_Gender'[Table_Measure_Gender],"M_",""),

    SUBSTITUTE('Table_Measure_Gender'[Table_Measure_Gender],"F_","")

)

Power Query - Create Rows Interval Dates (TO - FROM )

 let

    Source = DIM_SAP_Centro_CustoTXT,

    #"Changed Type1" = Table.TransformColumnTypes(Source,{{"DATEFROM", type date}, {"DATETO", type date}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each List.Dates([DATEFROM], Number.From([DATETO]-[DATEFROM])+1, #duration(1, 0, 0, 0))),

    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),

    #"Changed Type2" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type text}}),

    #"Filtered Rows" = Table.SelectRows(#"Changed Type2", each Text.StartsWith([Custom], "01")),

    #"Added Custom Column" = Table.AddColumn(#"Filtered Rows", "AnoMes", each Text.Combine({Date.ToText(Date.From([Custom]), "yyyy"), Date.ToText(Date.From([Custom]), "MM")}), type text),

    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom Column",{"AnoMes", "COSTCENTER", "TXTSH", "DATETO", "DATEFROM"})

in

    #"Removed Other Columns"

Power Query or SQL - CALCULATE DAY BETWEEN DATES

 Power Query:

Table.AddColumn(#"Changed Type1", "Subtraction", each Duration.Days([dt_Aberto] - [dt_Previsao]), Int64.Type)

SQL: 

SELECT DATEDIFF(year, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF(quarter, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF(month, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF(dayofyear, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF(day, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF(week, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF(hour, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF(minute, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF(second, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF(millisecond, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF(microsecond, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');

Tabular Editor Advanced = Search Filter sources

 

:Expression.ToUpper().Contains("APO")

:Name.EndsWith("Apo")

:Name.ToUpper().EndsWith("KEY")

Python - SpeedTest with Power BI

 

Python Code:

DAX - RANK

.Rank_ERRADO =

IF( HASONEVALUE(RFM_SAP_FAT_Vendas_py[CustomerID]),

RANKX(ALL (RFM_SAP_FAT_Vendas_py[CustomerID]), RFM_SAP_FAT_Vendas_py[.RankSum]))

.Rank2_CERTO =

IF( HASONEVALUE(RFM_SAP_FAT_Vendas_py[CustomerID]),

RANKX(ALL (RFM_SAP_FAT_Vendas_py[CustomerID]), RFM_SAP_FAT_Vendas_py[.RankSum]))

.RankSum = SUM(RFM_SAP_FAT_Vendas_py[TotalQtd])

OBS:  

.Rank2_CERTO e .RankSum são Measures

.Rank_ERRADO é Column Calc