Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi Friends,
I am facing issue in one of my report. I have 3 tables
Fact Incident Table
Fact Hour Table
Dim Date Table
Incident Table
Date | Incidents |
27-Jun-2021 | 75 |
01-Jun-2021 | 45 |
23-Jan-2021 | 85 |
20-Jan-2022 | 77 |
27-Mar-2021 | 54 |
01-Feb-2021 | 94 |
19-Jun-2020 | 79 |
16-Nov-2020 | 86 |
29-Sep-2021 | 82 |
11-Nov-2021 | 65 |
Hour Table
Date | Hours |
29-Jun-2021 | 1200 |
10-Jun-2021 | 3221 |
23-Jan-2021 | 1319 |
20-Jan-2022 | 1473 |
27-Mar-2021 | 1224 |
01-Feb-2021 | 1144 |
19-Jun-2020 | 1177 |
16-Nov-2020 | 1144 |
29-Sep-2021 | 1198 |
11-Nov-2021 | 1005 |
And we have convetional DATE table as usual.
Now i want to create a calculation which shows data for previous 12 months from the selected month.
For an example, i have a month slicer from DATE table and lets say if i select Nov 2021 it has to show the data for previous 12 months including Nov 2021 i.e. from Dec 2020 to Nov 2021 and Result should be like the below.
Month | Incidents | Hours |
Dec-20 | - | - |
Jan-21 | 85 | 1,319 |
Feb-21 | 94 | 1,144 |
Mar-21 | 54 | 1,224 |
Apr-21 | - | - |
May-21 | - | - |
Jun-21 | 120 | 4,421 |
Jul-21 | - | - |
Aug-21 | - | - |
Sep-21 | 82 | 1,198 |
Oct-21 | - | - |
Nov-21 | 65 | 1,005 |
Please help me on this
@amitchandak @Greg_Deckler @diex @aj1973 @BA_Pete @Jihwan_Kim
Solved! Go to Solution.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Thanks @Jihwan_Kim it is working perfectly for me.
Thank you very much for coming up with sample file.
@Infijeevan , when you select a month to want to display 12 months then you need an independent date table. But if need only total of 12 months you can use rolling
example
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))
//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(today(),-12)+1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))
refer video for diff
Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI
Hi @amitchandak
Thanks for the reply, but i am getting different result like for all the months i am getting same result from Hrs table.
It would be great if you can share the sample PBI file here.
Thanks in advance
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
143 | |
78 | |
63 | |
52 | |
47 |
User | Count |
---|---|
212 | |
82 | |
61 | |
60 | |
58 |