Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

16 REPLIES 16
Anonymous
Not applicable

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!

 

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
Not applicable

Sure.

This is how it looks in normal window,

chinmayshah16_0-1675826525825.png

 

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

 

chinmayshah16_1-1675826591581.png

 

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/

 

 

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
Not applicable

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?

 

 

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
Not applicable

Hello @SamInogic ,

Sometimes my values shows in error state,

chinmayshah16_0-1678840429154.png

 

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
Not applicable

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!

 

Anonymous
Not applicable

Hello @SamInogic ,

Sometimes my values shows in error state,

chinmayshah16_0-1678822831744.png

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 

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/

Anonymous
Not applicable

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 @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
Not applicable

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/






Anonymous
Not applicable

This is excellent. Thank you so much!!

SamInogic
Super User
Super User

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.

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors