Dates - Last 4 weeks, Next 4 weeks

[P0W3RB1]

Dates !!!! 🙂

In Dynamics CRM its very easy to pull off a report of for example all Orders older than X week, or Orders due in Next X weeks. Seems odd that Power BI doesnt have this simple function, dont you think?

There is a Microsoft provided content pack for Dynamics CRM sales, a pre-built dashboard and set of reports. I notice they have done exactly what i need to do, see screenshot. I need to create one called "Last4Weeks" and also need the ability to select truor or false, just like Microsoft have done here. How have they done this???

[hohlick]

Hello @P0W3RB1

You can add custom column with UI in Query Editor:

1. Select dates column
2. In "Add column" tab in "Date" group select Age
3. Select new column, in "Transformation" tab in "Duration" group select "Days"
4. Add custom conditions column with condition like "if [AgeFromDate]<28 and [AgeFromDate]>0 then true else false", name it Last4Weeks
5. Add custom conditions column with condition like "if [AgeFromDate]>-28 and [AgeFromDate]>0 then true else false", name it Next4Weeks
6. You can finetune where to add todays orders

[P0W3RB1]

Hi @hohlick at step 4 do you mean this condition box? if so i dont see an option for "And"

• Add custom conditions column with condition like "if [AgeFromDate]<28 and [AgeFromDate]>0 then true else false", name it Last4Weeks

[hohlick · Accepted solution]

You can edit it after this step manually, just add seciond condition in code

[P0W3RB1]

Thank you @hohlick this appears to have worked, thank you!