Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 ?
Solved! Go to 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.
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 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
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.
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
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)
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 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |