Last Month, Last Month last year, last month year -2 DAX

[Ctrl4pme]

Hi, 

I'm new in PowerBi and i'm motivate to learn how to use DAX(M language) 

I have done a period table, but now I'm trying to add my new period  : Last month, Last month last year, this month but last year, this month year minus 2.

It's possible to add this ? 

Thanks,

Phil

let
    TodaysDate = Date.From(DateTimeZone.FixedUtcNow()),
    Ranges = {
                {"Aujourd'hui", 
                TodaysDate, 
                TodaysDate, 
                1},
                {"Cette Semaine", 
                Date.From(Date.StartOfWeek(TodaysDate)), 
                TodaysDate, 
                2},
                {"Ce mois", 
                Date.From(Date.StartOfMonth(TodaysDate)), 
                TodaysDate, 
                3},
                {"Cette année", 
                Date.From(Date.StartOfYear(TodaysDate)), 
                TodaysDate, 
                4},
                {"7 derniers jours", 
                Date.AddWeeks(TodaysDate,-1) + #duration(1,0,0,0), 
                TodaysDate, 
                5},
                {"30 derniers jours", 
                Date.AddMonths(TodaysDate,-1) + #duration(1,0,0,0), 
                TodaysDate, 
                6},
                {"360 derniers jours", 
                Date.AddYears(TodaysDate,-1) + #duration(1,0,0,0), 
                TodaysDate, 
                7}

  
             },
    GetTables = List.Transform(Ranges, 
            each CreatePeriodTable(_{0}, _{1}, _{2}, _{3})),
    Output = Table.Combine(GetTables)
    
in
    Output

[v-ljerr-msft · Accepted solution]

Hi @Ctrl4pme,

If I understand you correctly, you should be able to use the formula below to add new period  : Last month, Last month last year, this month but last year, this month year minus 2. 

let
    TodaysDate = Date.From(DateTimeZone.FixedUtcNow()),
    ThisMonthLastYear = Date.AddYears(TodaysDate,-1),
    ThisMonthYearMinus2 = Date.AddYears(TodaysDate,-2),
    LastMonthDate = Date.AddMonths(TodaysDate,-1),
    LastMonthLastYear = Date.AddYears(LastMonthDate,-1),
    Ranges = {
                {"Aujourd'hui", 
                TodaysDate, 
                TodaysDate, 
                1},
                {"Cette Semaine", 
                Date.From(Date.StartOfWeek(TodaysDate)), 
                TodaysDate, 
                2},
                {"Ce mois", 
                Date.From(Date.StartOfMonth(TodaysDate)), 
                TodaysDate, 
                3},
                {"Cette année", 
                Date.From(Date.StartOfYear(TodaysDate)), 
                TodaysDate, 
                4},
                {"7 derniers jours", 
                Date.AddWeeks(TodaysDate,-1) + #duration(1,0,0,0), 
                TodaysDate, 
                5},
                {"30 derniers jours", 
                Date.AddMonths(TodaysDate,-1) + #duration(1,0,0,0), 
                TodaysDate, 
                6},
                {"360 derniers jours", 
                Date.AddYears(TodaysDate,-1) + #duration(1,0,0,0), 
                TodaysDate, 
                7},
                {"last month", 
                Date.From(Date.StartOfMonth(LastMonthDate)), 
                LastMonthDate, 
                8},
                {"last month last year", 
                Date.From(Date.StartOfMonth(LastMonthLastYear)), 
                LastMonthLastYear, 
                9},
                {"this month last year", 
                Date.From(Date.StartOfMonth(ThisMonthLastYear)), 
                ThisMonthLastYear, 
                10},
                {"this month year-2", 
                Date.From(Date.StartOfMonth(ThisMonthYearMinus2)), 
                ThisMonthYearMinus2, 
                11}  
             },
    GetTables = List.Transform(Ranges, 
            each CreatePeriodTable(_{0}, _{1}, _{2}, _{3})),
    Output = Table.Combine(GetTables)
    
in
    Output

Regards

[Ctrl4pme]

Great, it' working fine  🙂 

thank you for your help  

Have a good day 

Phil