jeudi 16 mai 2019

If then Statement using Two Different Date Ranges

In Power BI, I'm using a period table that applies steps to take today's date, sets up the ranges, transforms the ranges into a table, and then sets the output in Power Query. Everything works as it is supposed to but the focus is at the ranges step.

Here it is as is:

    = {
                    {"Day", 
                    Date.AddDays(TodaysDate,-1), 
                    Date.AddDays(TodaysDate,-1),
            1},
                    {"Week to Date", 
                    Date.From(Date.StartOfWeek(TodaysDate,Day.Thursday)), 
                    Date.AddDays( Date.From(Date.StartOfWeek(TodaysDate,Day.Thursday)) , +6), 
            2},
                    {"Week", 
                    Date.AddWeeks( Date.From(Date.StartOfWeek(TodaysDate,Day.Thursday)) , -1), 
                    Date.AddDays( Date.From(Date.StartOfWeek(TodaysDate,Day.Thursday)) , -1), 
....

Here is the example output:

enter image description here

What I want however, is that if today is equal to Monday, then only show the date for Friday. Here was my attempt:

= {
            {"Day", 
            if Date.DayofWeek(TodaysDate) = 1 
       then 
    [
            Date.AddDays(TodaysDate,-3), 
            Date.AddDays(TodaysDate,-3),
    ]
            else
    [
            Date.AddDays(TodaysDate,-1), 
            Date.AddDays(TodaysDate,-1),
    ] ....

Notice that "Day" is "Yesterday". I'm trying to get this done so people can see production for the previous day worked where the previous day for Monday would be Friday.

Aucun commentaire:

Enregistrer un commentaire