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
hideakisuzuki01
Helper II
Helper II

DA Question: Display 6 months after a selected month in slicer

Hi,

I was able to get the data I needed after some trial and errors but I dont understand how/why it works so I would appreciate if someone could help me.

 

I have this DAX formula 

Sales(Next6Months) = CALCULATE(SUM(Sales[Amount]),DATESINPERIOD('Calendar'[Date],MAX('Calendar'[Date]),-6 ,MONTH))

 

When I choose a period like 201905, it gives me the next months, 201905-201910, just as I wanted.  But why do I need a - 6 in the formula?  If I use 6 instead of -6, then it will give me the last 6 months instead. 

 

Should this be the other way around ?  

 

1 ACCEPTED SOLUTION

Hi, @hideakisuzuki01 

The relation between two tables in your pbix file is one to many, the table Calendar is the one side of this relationship and ‘受注’ is the many side. The cross filter direction between these two tables is Single. Therefore, the filter will pass from Calendar to ‘受注’. However, in your report, MonthYear Field from Calendar is used as Columns of Matrix visual and YearMonth Field from ‘受注’ is used to create Slicer. Then, the filter will not pass from ‘受注’ to Calendar.

 

v-cazheng-msft_1-1611892255354.png

 

For the formula DATESINPERIOD('Calendar'[Date],MAX('Calendar'[Date]),-6 ,MONTH), it will return past 6 months before MAX(‘Calendar’[Date]) in the Calendar[Date] column.

 

For the pbix file you provided, I use MonthYear to create Slicer and YearMonth as Columns of Matrix. Then it will pass the filter correctly. DATEISINPERIOD will work according to the syntax.

 

Here is the pbix.

 

Best Regards,

Caiyun Zheng

View solution in original post

9 REPLIES 9
hideakisuzuki01
Helper II
Helper II

Thanks Caiyun !

I confirmed that it works as you described, your explanation makes perfect sense.  

I really appreciate your help on this !

 

It's my pleasure to cope with this issue for you. Have a nice day!

 

Best Regards,

Caiyun Zheng

v-cazheng-msft
Community Support
Community Support

Hi, @hideakisuzuki01 

According to syntax of DATESINPERIOD, the parameter number of intervals specifies the number of intervals to add to or subtract from start date. If it is positive, dates are moved forward in time. If it is negative, dates are move backward in time. For more details about DATESINPERIOD, you can refer this article. I also confused about the result you get. Can you provide a sample removing sensitive data or describe more details about your report?

 

Best Regards,

Caiyun Zheng

 

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks Caiyun

There is no sensitive data so I would like to give you a copy of the .pbix file but it does not seem like its possible through this community. 

How should I give you the data and a copy of the report ?

 

Regards

Hi, @hideakisuzuki01 

You can upload your pbix file to OneDrive for Business and then copy the link of the file in your reply.

 

Best Regards,

Caiyun Zheng

Here you go

https://1drv.ms/u/s!AsVrMTKK1CYajSEEcVPnGqS004uq?e=x9ek3K

 

Please let me know if you have any trouble downloading the pbix file.

Regards

Hi, @hideakisuzuki01 

The relation between two tables in your pbix file is one to many, the table Calendar is the one side of this relationship and ‘受注’ is the many side. The cross filter direction between these two tables is Single. Therefore, the filter will pass from Calendar to ‘受注’. However, in your report, MonthYear Field from Calendar is used as Columns of Matrix visual and YearMonth Field from ‘受注’ is used to create Slicer. Then, the filter will not pass from ‘受注’ to Calendar.

 

v-cazheng-msft_1-1611892255354.png

 

For the formula DATESINPERIOD('Calendar'[Date],MAX('Calendar'[Date]),-6 ,MONTH), it will return past 6 months before MAX(‘Calendar’[Date]) in the Calendar[Date] column.

 

For the pbix file you provided, I use MonthYear to create Slicer and YearMonth as Columns of Matrix. Then it will pass the filter correctly. DATEISINPERIOD will work according to the syntax.

 

Here is the pbix.

 

Best Regards,

Caiyun Zheng

Thanks a lot. I've downloaded it successfully.

 

Best Regards,

Caiyun Zheng

pranit828
Community Champion
Community Champion

HI @hideakisuzuki01 

Chek the syntax of DATESINPERIOD. 

DATESINPERIOD function (DAX) - DAX | Microsoft Docs

You need -6 to go 6 prior months from the max (date) month by month.

If you choose 6 then it will give you next 6 months from max(date)





PBI_SuperUser_Rank@1x.png


Hope it resolves your issue? 
Did I answer your question? Mark my post as a solution!

Appreciate your Kudos, Press the thumbs up button!!
Linkedin Profile

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.