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 All,
I am creating a report using SSAS Tabular live connection. Client wants to report all the measures should be created in cube itself. I tried 7 Day Avg below measure in cube, it is not giving the exact result what i am looking. In my report i am using two dates one is record date is as date slicer and production date is in report. I want to show only one aggregated value for the 7 day avg that should be aggregate of past 7 days record date moving average and latest production date as showin below screen shot. In my case, the measure is splitting into 7 parts when i inlude production date in report. I want to show only one record for one day record date slicer not 7 days splitted below.
Expression i used:
7-Day Avg =
Var Intervel = -7
RETURN
CALCULATE(
SUM(Procount[Net Flow Rate]),
DATESINPERIOD(Procount[Record Date],LASTDATE(Procount[Record Date]),Intervel,DAY))
/
7
Result i am getting when i add produciton date in report:
Desired Result:
Gas production and net flow rate both are same base measures.
7-Day avg & Rolling 7 day sum both are same.
Solved! Go to Solution.
Hi @Anonymous,
It looks like a context issue. If you put the [GasProduction(sum)] and the [Rolling 7 day SUM] along with the [Production Date], I think you will get the right result.
I would still suggest you create a date table. The dates in the visual will be from this table.
Calendar = calendar(date(2010, 1,1), date(2019, 12, 31))
Best Regards,
Hi @Anonymous,
Could you please mark the proper answers as solutions?
Best Regards,
Hi @Anonymous,
It looks like a context issue. If you put the [GasProduction(sum)] and the [Rolling 7 day SUM] along with the [Production Date], I think you will get the right result.
I would still suggest you create a date table. The dates in the visual will be from this table.
Calendar = calendar(date(2010, 1,1), date(2019, 12, 31))
Best Regards,
Hi @Anonymous,
Firstly, the datesinperiod-function-dax is a time intelligence function that needs a continuous date table.
Secondly, the dates in the formula are [Record Date] while it's [Date] in the visual. That could cause an issue.
Can you share a sample file, please? Please mask the sensitive parts first.
Best Regards,
Here is my sample data. Where i am getting proper ouput from import method and Live connection i am calling this measure from cube. Due to this it is not giving desired output.
Input:
Measure | Date |
449.3890785 | 12/31/2018 0:00 |
449.378157 | 12/30/2018 0:00 |
461.4709898 | 12/29/2018 0:00 |
463.4600683 | 12/28/2018 0:00 |
473.487372 | 12/27/2018 0:00 |
466.3945392 | 12/26/2018 0:00 |
482.465529 | 12/25/2018 0:00 |
Required Result | |
Measure | Date |
463.7208191 | 12/31/2018 0:00 |
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...
Hi Greg, Thanks for your reply. My scenario is different, problem still exists same.
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 |
---|---|
107 | |
98 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |