cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Smokey24 Helper I
Helper I

Date variables with custom calender (show last 3 week, show last 3 months etc.)

Hi all,

 

I am using a 5-4-4 calender and would need some help with date variables for charts.

For example if I have a bar chart I want it to only show me the last 6 months (the current month should be excluded).

So given the current month is April I want the chart to show me March, Feb, Jan, Dec, Nov, Oct.

The same with weeks, I want to have the last 12 weeks excluding the current one.

 

Since using a custom calender I can't use time intelligence. 

 

Do you guys have any suggestions?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: Date variables with custom calender (show last 3 week, show last 3 months etc.)

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

 

Would need sample data, but you should be able to create a column in your date table for how many weeks/months etc in the past each day is and then you could simply filter for > 0 and < 6 for example.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

2 REPLIES 2
Super User IV
Super User IV

Re: Date variables with custom calender (show last 3 week, show last 3 months etc.)

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

 

Would need sample data, but you should be able to create a column in your date table for how many weeks/months etc in the past each day is and then you could simply filter for > 0 and < 6 for example.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

Smokey24 Helper I
Helper I

Re: Date variables with custom calender (show last 3 week, show last 3 months etc.)

Yes, I had the same idea and tried it. 

It works! I created a month sequence starting from my mindate up to my maxdate and created Variables.

One MaxMonth (should be current month) and MinMonth (is MaxMonth -x) just said in the Date Calender, if the month sequence is between MaxMonth-x and MaxMonth-1 give me a 1 else a 0.

Then I use this Variables in a measure.

 

Took me quite a while to think about it, but actually it's pretty easy.

So it's quite similar to your suggestion I just saw.

 

Thank you! 

Helpful resources

Announcements
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors