cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chinmayshah16
Regular Visitor

Display Last N months from today's date in Marks card

Hello , can someone please help?

I have created, below filter,

 

chinmayshah16_0-1673483250971.png

In the marks card I want to display today's date minus what ever the option I pick from the slicer,

So if today is 11 January, 2023 and user pick 'last 6 months' from the slicer then I would like to display, 

(11 July, 2022 - 11 January, 2023)

It should keep on changing based on what user pick from the slicer.

How can I do that? Please help!

 

1 ACCEPTED SOLUTION

Hi,

Thanks for providing the Power BI file.

We have checked and noticed that you only want to display range of dates based on Selection in your slicer

“Showing” i.e. Last 6 months etc.


Please follow below listed steps in your Power BI report to achieve the Date Range display as expected,

Within “Sort By” table, create a Measure “Min Range Date" with below DAX expression

Min Range Date = IF(SELECTEDVALUE('Period_Fact Table'[In the last]) = "Last Month", DATE(YEAR(TODAY()), MONTH(TODAY())-1, DAY(TODAY())),IF(SELECTEDVALUE('Period_Fact Table'[In the last]) = "Last 3 Months",  DATE(YEAR(TODAY()), MONTH(TODAY())-3, DAY(TODAY())),

IF(SELECTEDVALUE('Period_Fact Table'[In the last]) = "Last 6 Months",  DATE(YEAR(TODAY()), MONTH(TODAY())-6, DAY(TODAY())),

IF(SELECTEDVALUE('Period_Fact Table'[In the last]) = "Last Year",  DATE(YEAR(TODAY())-1, MONTH(TODAY()), DAY(TODAY())),

IF(SELECTEDVALUE('Period_Fact Table'[In the last]) = "Last 2 Years",  DATE(YEAR(TODAY())-2, MONTH(TODAY()), DAY(TODAY())),TODAY())))))

Please refer to the below screenshot for the same.

 

SamInogic_0-1673600396179.png

 

Now to display Date Range String create a new Measure in “Sort By” table with DAX expression as:

Date Range = "(" & CONVERT(FORMAT([Min Range Date],"dd mmmm yyyy"), STRING) & " - " &  CONVERT(FORMAT(TODAY(),"dd mmmm yyyy"), STRING) & ")"

 

SamInogic_1-1673600410319.png

 

Add a Card Visual on report and select this Date Range column from Sort By Table to be displayed as shown in the below screenshot.

SamInogic_2-1673600436226.png


If this answer helps, please mark it as Accepted Solution so it would help others to find the solution.


Thanks!

Inogic Professional Service Division

An expert technical extension for your techno-functional business needs

Power Platform/Dynamics 365 CRM

Drop an email at crm@inogic.com

Service:  http://www.inogic.com/services/ 

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/

View solution in original post

8 REPLIES 8
chinmayshah16
Regular Visitor

Hi @SamInogic, Thank you so much for the response.

Here I have pbix file for your reference.

https://drive.google.com/file/d/1s3WLU4yfdEF9ZppDqqg5lG6PSqWZ7JLk/view?usp=sharing 

This is where I would like to dipslay date,

chinmayshah16_0-1673588548499.png

Can you please help in this file how can I show it there? Please and thanks!

 

Hi,

Thanks for providing the Power BI file.

We have checked and noticed that you only want to display range of dates based on Selection in your slicer

“Showing” i.e. Last 6 months etc.


Please follow below listed steps in your Power BI report to achieve the Date Range display as expected,

Within “Sort By” table, create a Measure “Min Range Date" with below DAX expression

Min Range Date = IF(SELECTEDVALUE('Period_Fact Table'[In the last]) = "Last Month", DATE(YEAR(TODAY()), MONTH(TODAY())-1, DAY(TODAY())),IF(SELECTEDVALUE('Period_Fact Table'[In the last]) = "Last 3 Months",  DATE(YEAR(TODAY()), MONTH(TODAY())-3, DAY(TODAY())),

IF(SELECTEDVALUE('Period_Fact Table'[In the last]) = "Last 6 Months",  DATE(YEAR(TODAY()), MONTH(TODAY())-6, DAY(TODAY())),

IF(SELECTEDVALUE('Period_Fact Table'[In the last]) = "Last Year",  DATE(YEAR(TODAY())-1, MONTH(TODAY()), DAY(TODAY())),

IF(SELECTEDVALUE('Period_Fact Table'[In the last]) = "Last 2 Years",  DATE(YEAR(TODAY())-2, MONTH(TODAY()), DAY(TODAY())),TODAY())))))

Please refer to the below screenshot for the same.

 

SamInogic_0-1673600396179.png

 

Now to display Date Range String create a new Measure in “Sort By” table with DAX expression as:

Date Range = "(" & CONVERT(FORMAT([Min Range Date],"dd mmmm yyyy"), STRING) & " - " &  CONVERT(FORMAT(TODAY(),"dd mmmm yyyy"), STRING) & ")"

 

SamInogic_1-1673600410319.png

 

Add a Card Visual on report and select this Date Range column from Sort By Table to be displayed as shown in the below screenshot.

SamInogic_2-1673600436226.png


If this answer helps, please mark it as Accepted Solution so it would help others to find the solution.


Thanks!

Inogic Professional Service Division

An expert technical extension for your techno-functional business needs

Power Platform/Dynamics 365 CRM

Drop an email at crm@inogic.com

Service:  http://www.inogic.com/services/ 

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/

Hi @SamInogic , If I want to display the same data between the dates which I am showing it here then what are the changes I need to make in a code?

Like here dates are showing,

Here I am showing this range for the last 6 months, 19 July, 2022 to 19 Jan, 2023

chinmayshah16_0-1674140448914.png

But for the slicer 'Last 6 months' as per the code it's showing data between 23 July, 2022 to 19 Jan, 2023:

Code,

chinmayshah16_1-1674140547855.png

 

So instead of days how can I mention months here? or how can I mention days instead of months and year for the title. I want both the codes at the same level. Either Months/Years or Days. Can you please help?

 

 

 

Hi @chinmayshah16,


Can you try to add below DAX expression for your “Period_Fact Table” table,

Period_Fact Table =

VAR

    _lastMonth = ADDCOLUMNS(

        CALCULATETABLE(

            'sem FACT_INVOICE_DETAIL'

            ,DATESBETWEEN('sem FACT_INVOICE_DETAIL'[INVOICE DATE_FACT],DATE(YEAR(TODAY()), MONTH(TODAY())-1, DAY(TODAY())), TODAY())

        )

        , "In the last", "Last Month"

    )

VAR

    _last3Months = ADDCOLUMNS(

        CALCULATETABLE(

            'sem FACT_INVOICE_DETAIL'

            ,DATESBETWEEN('sem FACT_INVOICE_DETAIL'[INVOICE DATE_FACT],DATE(YEAR(TODAY()), MONTH(TODAY())-3, DAY(TODAY())), TODAY())

        )

        , "In the last", "Last 3 Months"

    )

VAR

    _last6Months = ADDCOLUMNS(

        CALCULATETABLE(

            'sem FACT_INVOICE_DETAIL'

            ,DATESBETWEEN('sem FACT_INVOICE_DETAIL'[INVOICE DATE_FACT],DATE(YEAR(TODAY()), MONTH(TODAY())-6, DAY(TODAY())), TODAY())

        )

        , "In the last", "Last 6 Months"

    )

VAR

    _lastYear = ADDCOLUMNS(

        CALCULATETABLE(

            'sem FACT_INVOICE_DETAIL'

            ,DATESBETWEEN('sem FACT_INVOICE_DETAIL'[INVOICE DATE_FACT],DATE(YEAR(TODAY())-1, MONTH(TODAY()), DAY(TODAY())), TODAY())

        )

        , "In the last", "Last Year"

    )

VAR

    _last2Year = ADDCOLUMNS(

        CALCULATETABLE(

            'sem FACT_INVOICE_DETAIL'

            ,DATESBETWEEN('sem FACT_INVOICE_DETAIL'[INVOICE DATE_FACT],DATE(YEAR(TODAY())-2, MONTH(TODAY()), DAY(TODAY())), TODAY())

        )

        , "In the last", "Last 2 Years"

    )

RETURN

    UNION(_lastMonth, _last3Months, _last6Months, _lastYear, _last2Year )


Thanks!

Inogic Professional Service Division

An expert technical extension for your techno-functional business needs

Power Platform/Dynamics 365 CRM

Drop an email at crm@inogic.com

Service:  http://www.inogic.com/services/ 

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/

Thank you so much. It did work!

Hi,

Happy to hear that solution works for you!


If the answer helps, please mark it as Accepted Solution so it would help others to find the solution.


Thanks!

Inogic Professional Service Division

An expert technical extension for your techno-functional business needs

Power Platform/Dynamics 365 CRM

Drop an email at crm@inogic.com

Service:  http://www.inogic.com/services/ 

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/






This is excellent. Thank you so much!!

SamInogic
Resolver III
Resolver III

Hi @chinmayshah16 ,

To display the data filtered based on the Date period i.e. last N Months then you can follow below process in Power BI report.

1. Create a Calendar Table, which will be used in a Slicer to be used for Data filtering as Last N Months, Next N Months etc.


In Power BI report, navigate to Modelling tab and click on New Table (considered range will be 1/1/2000 to 5 years more than current Year) with below DAX expression.


SelectionDateRange = CALENDAR(DATE(2000,1,1),DATE(YEAR(TODAY()) + 5,1,1))

2. Once table is created, add a Slicer Control from Visuals and add the Date field from SelectionDateRange Table. Navigate to Slicer Settings and change the Option Style as Relative Date.

Please refer to the below screenshot for the same.

SamInogic_0-1673586318540.png

3. Now we have considered sample Table that include data that need to be filtered as shown in below screenshot.

SamInogic_1-1673586338217.png


4. In order to filter based on slicer date need to create a relationship between both table as shown in below screenshot,

SamInogic_2-1673586360568.png

5. Now you can filter data using slicer as Last 6 Months and data will be displayed as per the date range.


Please refer to the below screenshot for the same,

SamInogic_3-1673586379832.png


If this answer helps, please mark it as Accepted Solution so it would help others to find the solution.

Thanks!

Inogic Professional Service Division

An expert technical extension for your techno-functional business needs

Power Platform/Dynamics 365 CRM

Drop an email at crm@inogic.com

Service:  http://www.inogic.com/services/ 

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.