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

[chinmayshah16]

Hello , can someone please help?

I have created, below filter,

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!

[chinmayshah16]

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,

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

[SamInogic · 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.

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) & ")"

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.

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/

[chinmayshah16]

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

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

Code,

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?

[SamInogic]

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/

[chinmayshah16]

Thank you so much. It did work!

[SamInogic]

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/

[chinmayshah16]

This is excellent. Thank you so much!!

[SamInogic]

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.

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

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

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,

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/