Creating a 30/60/90 day column in power bi

DAX DAX Calculations
1

Hi Everyone,

How can I create a calculated column in power bi that can look at the past 30/60/90 days?

2

For something like this, I would recommend doing it in Power Query, in your date table.
NOTE, this means refreshing your data at least once a day

Adding a custom column that looks at the row date and compares it to today’s date, adding the 30, 60, 90 day flag if is hits the criteria.

Here’s the M Code, and I’ve also attached a PBIX so you can see how this is working, the bonus here is that you can use the ranges as a filter since its in your datasource.

let
Source = List.Dates( #date(2020, 7, 1), 180, #duration(1,0,0,0)),
// for this example, source is only to get a list of dates to work with
// (not needed if working with an existing date table)

CurrentDate = DateTime.Date( DateTime.FixedLocalNow ()),
   // Current Date is not connected to any step in this code 
   // (not Source, or Converted to Table) - 
   // it is used IN the "Added Custom" step

#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), 
      null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",
     {{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",
     {{"Date", type date}}),
// these steps are not needed if working with an existing date table

#"Added Custom" = Table.AddColumn
  (#"Changed Type", "Past Range", each 
  if [Date] < Date.AddDays( CurrentDate, -90 ) then null else 
  if [Date] <= Date.AddDays( CurrentDate, -60 ) then "90 days" else 
  if [Date] <= Date.AddDays( CurrentDate, -30 ) then "60 days" else 
  if [Date] > CurrentDate then null else "30 days" )
// this is the only step that creates the date ranges - 
// using the Current Date which was added at step two for comparison

in
#“Added Custom”

Date Table adding date range logic.pbix (17.5 KB)

2 Likes
3

@Tausif124,

Here is another way to accomplish this in Power Query, if you wish to use those periods in a slicer.
Here is the M Code:

let
    TodaysDate = Date.From(DateTimeZone.FixedUtcNow()),
    Ranges = {
                {"Today", TodaysDate, TodaysDate, 1},
                {"Yesterday", Date.AddDays(TodaysDate,-1), Date.AddDays(TodaysDate,-1), 2},
                {"Last 30 Days",  Date.AddDays(TodaysDate,-30), TodaysDate, 3},
                {"Last 60 Days",  Date.AddDays(TodaysDate,-60), TodaysDate, 4},
                {"Last 90 Days",  Date.AddDays(TodaysDate,-90), TodaysDate, 5}
                                   
             },
    fxCreatePeriodTabe = ( PeriodName as text, StartDate as date, EndDate as date, SortOrder as number ) as table =>
        let
            DayCount = Duration.Days(EndDate-StartDate)+1,
            DateList = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
            AddPeriodName = List.Transform(DateList, each {PeriodName,_,SortOrder}),
            CreateTable = #table( type table[Period=text, Date=date, Sort=number], AddPeriodName)
        in
            CreateTable,
    
    GetTables = List.Transform(Ranges, each fxCreatePeriodTabe(_{0}, _{1}, _{2}, _{3})),
    Output = Table.Combine(GetTables)
in
    Output

I’ve also attached the PBIX

EDNA 30-60-90 Slicer.pbix (1.8 MB)

Thanks
Jarrett

2 Likes
4

Thank you so much!

5

Hi @Tausif124, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

6

Hi @Tausif124

Welcome to the forum.

I just want to share another approach using DAX. You can use those periods in Slicer.

image
image1418Ă—776 53.7 KB

image
image253Ă—546 5.1 KB

1 Like
7

Similar to this, how can I create a table or dax to calculate projects that starts in 30, 60, 90 days. Thanks

8

Hi there,

please start a new topic as this topic is closed 3 years ago.

You will be better results starting a new topic as members of the forum usually don’t view closed topics.

thanks
Keith

1 Like