SSRS Query to Dax

[Anonymous]

Hi All, Please provide the DAX folmula for the below Sql Statement and sample data for your reference. SUM (GasProduction/7) OVER (ORDER BY RecordDate ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS Avg7DayGas Input: RecordDate NetFlowRate 1/2/2019 449.4054608 1/1/2019 449.3890785 12/31/2018 449.378157 12/30/2018 461.4709898 12/29/2018 463.4600683 12/28/2018 473.487372 12/27/2018 466.3945392 Avg7DayGas=SUM(449+449+449+461+463+473+463+466)/7 Output: 1/2/2019 458.9979522 Thanks

[v-yuta-msft]

@Anonymous,

In power bi, you can create a calculate column using DAX below:

Avg7DayGas = 
VAR previous_7_date = Table1[RecordDate] - 7
VAR current_date = Table1[RecordDate]
RETURN
    CALCULATE (
        AVERAGE ( Table1[NetFlowRate] ),
        FILTER (
            Table1,
            Table1[RecordDate] > previous_7_date
                && Table1[RecordDate] <= current_date
        )
    )

 

Regards,

Jimmy Tao

[Anonymous]

Hi @v-yuta-msft, Thanks for your reply. But i want a single record which shows max of record date and avg7daygas in Power Bi report, i dont want to show the remaining 6 records. Please find the below input and desired output for your reference. Please provide the dax query. SQL Server Input RecordDate NetFlowRate Avg7DayGas 1/2/2019 449.4054608 458.9979522 1/1/2019 449.3890785 394.7971721 12/31/2018 449.378157 330.5987323 12/30/2018 461.4709898 266.4018528 12/29/2018 463.4600683 200.4774256 12/28/2018 473.487372 134.2688445 12/27/2018 466.3945392 66.62779132 Power BI Output RecordDate NetFlowRate Avg7DayGas 1/2/2019 449.4054608 458.9979522 Thanks

[v-yuta-msft]

@Anonymous,

Ok, to generate a table, you can then click Modeling-> New Table and use DAX below:

Output = NATURALINNERJOIN(SUMMARIZE(Table1, Table1[RecordDate], Table1[NetFlowRate], Table1[Avg7DayGas]), LASTDATE(Table1[RecordDate]))

 

Regards,

Jimmy Tao

[Anonymous]

I don't want to generate a table for this calculation. Values are not coming as correct, when i do the table operation. I want the record date to be descending order and based on record date i need to show preceding 7 days average as single record as per the below SSRS report code. I am able to see the correct values in SSRS report. SELECT ProductionDate,Netflowrate, SUM (GasProduction/7) OVER (ORDER BY RecordDate ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS Avg7DayGas Thanks

[v-yuta-msft]

@Anonymous,

I don't want to generate a table for this calculation. Values are not coming as correct, when i do the table operation. I want the record date to be descending order and based on record date i need to show preceding 7 days average as single record as per the below SSRS report code. I am able to see the correct values in SSRS report. SELECT ProductionDate,Netflowrate, SUM (GasProduction/7) OVER (ORDER BY RecordDate ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS Avg7DayGas Thanks

---

Your clarification of your requirement is not so clear. Can you show the output table you want?

Regards,

Jimmy Tao

[Anonymous]

I am getting the below output in SQL Server, the same i want in Power BI. As per the SSRS Query condition the report is pulling only max of record date for the Avg7DayGas. RecordDate NetFlowRate Avg7DayGas 2019-01-02 449.405460750853 458.997952218427 2019-01-01 449.389078498294 394.797172111163 2018-12-31 449.378156996587 330.598732325693 2018-12-30 461.470989761092 266.401852754752 2018-12-29 463.460068259386 200.477425646025 2018-12-28 473.487372013652 134.268844466113 2018-12-27 466.394539249147 66.627791321306

[v-yuta-msft · Accepted solution]

@Anonymous,

Modify the formula as below:

Avg7DayGas = 
VAR previous_7_date = Table1[RecordDate] - 7
VAR current_date = Table1[RecordDate]
RETURN
    CALCULATE (
        SUM ( Table1[NetFlowRate] ),
        FILTER (
            Table1,
            Table1[RecordDate] > previous_7_date
                && Table1[RecordDate] <= current_date
        )
    )
        / 7

 

Regards,

Jimmy Tao

[Anonymous]

@v-yuta-msft

I tried your query in below screen shot it is giving me as same as gas production(net flow rate). I want the output as 464. 

[Anonymous]

Hi @v-yuta-msft

When i create the below measure in 3rd screen shot it is working fine as it is giving output as 1 aggregated record. I tried the same in live connection ( created measure in cube itself like 4th screen shot) it is not working fine. It is splitting into 7 records( 1st screen shot.)

Desired Result: (Import working fine)- 2nd & 3rd Screen shot.

Power Bi Import method the below query returning exact output as mentioned in  2nd screen shot.

Cube query not working in Power BI- 1st & 4th Screen shot.

I written the same logic in SSAS Tabular Cube as a measure except allselected(Table) but it is not giving exact output like 2nd screen shot. I am not able to replicate the same because in live connection has many tables i am not able to replicate all tables in allselected function.

[Anonymous]

It is working fine now. Thanks Jimmy for your reply.