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

[Anonymous]

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!

[Anonymous]

Hello @SamInogic, I have one question. In the same report I have turn on 'Responsiveness' -> "ON" but when I try Power Bi Playground from here, https://playground.powerbi.com/en-us/explore-features and try to change the window size then bar chart doesn't work as responsiveness should work. Is there any settings am I missing? Can you please guide. Thanks a lot!

[SamInogic]

Hi @Anonymous,

If possible can you share the Screenshot for the Chart not being responsive at your end, so that I can get idea regarding issue you are facing?

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/

[Anonymous]

Sure.

This is how it looks in normal window,

And if I resize the window to smaller size then viz just zoom in,

This is what I expect Responsiveness to work when I narrow down the window,

https://powerbi.microsoft.com/fr-ch/blog/responsive-visualizations-coming-to-power-bi/

[SamInogic]

Hi @Anonymous,

In Power BI the Responsive setting is per visual not per the report, so the graph will work as responsive when we change height/width of that visual. As for report level we did not have a Responsive setting and thus changing Windows height/width (by Zoom in or Zoom out) will not change the visual resolution.

This Responsive Setting all means for Visual to be Responsive in Power BI Report.

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/

[Anonymous]

Oh @SamInogic , If you check the below reference link then this is what I am trying with embeded link of my report but viz not changing like how it is changing in below reference link.

https://powerbi.microsoft.com/fr-ch/blog/responsive-visualizations-coming-to-power-bi/

Is this how I should check the responsiveness of my report/viz? Or I should check it out some other way?

[SamInogic]

Hi @Anonymous 

Yes, we think only this is the setting for Responsive for the Power BI. Currently, it does not seem any other way to check the responsiveness of the report.

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/

[Anonymous]

Hello @SamInogic ,

Sometimes my values shows in error state,

Other should calculate as - Total Spend - (Net fees + Net Expenses) = Other

But it shows in error state, how can I fix this? I have reference file here. It's in tooltip. Please help. Thanks!

Ref file:

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

please help!!

[Anonymous]

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!

[Anonymous]

Hello @SamInogic ,

Sometimes my values shows in error state,

Other should calculate as - Total Spend - (Net fees + Net Expenses) = Other

But it shows in error state, how can I fix this? I have reference file here. It's in tooltip. Please help. Thanks!

Ref file:

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

[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/

[Anonymous]

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 @Anonymous,

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/

[Anonymous]

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/

[Anonymous]

This is excellent. Thank you so much!!

[SamInogic]

Hi @Anonymous ,

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/