DAX IDEAS PLEASE

[christianfcbmx]

Dear Colleagues Im not getting so smart today and I need your help:

Im trying the following: I need to create a chart out of my data always getting the last 5 or 4 weeks (Axis Y quantities, axis X week number) (Hopefully with an option to choose how many weeks you want to show). the next step is to create 1 chart for each week of the last 5 or 4 week chosen of my first chart (First chart week 1, second chart week 2 and so on).

Im not that good at DAXING in power BI yet and Ive spent hours trying to figure something out with no results...Anyone have an idea or a dax already done to share?

sample files: https://www.dropbox.com/s/f99yqz9my1nyjx6/Counting%20weeks%202.pbix?dl=0

Thank you guys in advance!!!

[Ashish_Mathur]

Hi,

I think i can help you with answering the first question.  Share some data and show your expected result there.

[christianfcbmx]

Hi @Ashish_Mathur... look what I did with the months is what I need with the weeks (I was thinking about an index column) tell me what you think....thanks in advance!

https://www.dropbox.com/s/f99yqz9my1nyjx6/Counting%20weeks%202.pbix?dl=0

[Ashish_Mathur]

Hi,

You have to use the CALCULATE, COUNT and DATESBETWEEN functions in a measure.  Somehow when i try it, i get a syntax error and cannot figure out why i get that error.

[mbenjamin]

@christianfcbmx

This doesn't solve your immediate problem, but I thought you might be interested in this upcoming webinar about Practical DAX.

It's airing April 3rd. 🙂

[christianfcbmx]

do you know the ending time of the webinar? lenght?

[mbenjamin]

@christianfcbmx Apologies for the late response! The webinar is available on demand and is approximately 60 minutes long. 

[christianfcbmx]

Thanks for that information, It sounds awesome and Ill be there! Thank you again @mbenjamin

[ChrisMendoza]

Hello @christianfcbmx, 

Possibly a Relative Slicer is part of the solution? Using the [Date] from 'dCalendar'.

Click the little Down caret

Select Relative

Select the Drop-Down caret

Select the built-in filter criteria

[christianfcbmx]

Hi @ChrisMendoza, I didnt realize that one... eventhough I was playing with "relative date filtering" in the visual and it work for months no for weeks... and to make it work correctly for months I made a DAX... any native power BI feature for what I was looking for worked properly without getting smart and add a DAX to make it work... the native solution you share doesnt work 100%... I chose the last 4 weeks and it shows 2... take a look at my pbix. and play... my DAX for month could be usefull for you (keep it) and tell me what you think 🙂

[ChrisMendoza]

@christianfcbmx, 

I agree, the pre-built Relative Slicer is not 100% because your fact table has not been updated since March 1, 2018. Last 4 weeks (calendar) from today is March 3, 2018; therefore 'no data' is displayed. The same is said for Last 4 Weeks from today is March 6, 2018.

Seems your solution is something along the lines of Find the last date in the fact table, do some math to find the last x weeks, then do some more math (e.g. sum) based off the x results.

I think @Ashish_Mathur's comment is probably the direction you need to head.

[christianfcbmx]

Still work it on it with no results...If I figure something out Ill let you guys know , @ChrisMendoza, @Ashish_Mathur