how to find Last 12 days average egg production from Production Table

[arifulice]

Dear all,

I have a production table with several columns, I want to find out the average egg production of last 12 days.

I have written the following code, but the result giving the data of all month and year. what is the problem

EggProductionOfLast12Days :=
CALCULATE (
    AVERAGE ( FactProductionData[Egg Prod#Qty] ),
    FILTER (
        FactProductionData,
        FactProductionData[Date] <= LASTDATE ( FactProductionData[Date] )
            && FactProductionData[Date]
                >= LASTDATE ( FactProductionData[Date] ) - 12
    )
)

[Ashish_Mathur]

Hi @arifulice,

Try this

1. Create a calendar table with dates spanning the ones you have in your FactProductionData table
2. Create a relationship from the Date column of your FactProductionData table to the Date column of your Calendar table
3. Drag the Date column from your calendar table to filter/slicer section of your visual
4. Select a date in the filter/slicer section
5. Write this calculated field formula

=CALCULATE(AVERAGE(FactProductionData[Egg Prod#Qty]),DATESBETWEEN(Calendar[Date],MIN(Calendar[Date])-11,MAX(Calendar[Date])))

Hope this helps.

[MFelix]

Hi @arifulice,

Try to change the LASTDATE by
DATEADD( FactProduction[Date], -12, DAY)

Regards,
MFelix

[arifulice]

Hello MFelix ,

After changing the lastdate by the followiung code, the result is remaining the same like giving the result for month Jan to December. 

EggProductionOfLast12Days:=CALCULATE (
    AVERAGE ( FactProductionData[Egg Prod#Qty] ),
    FILTER (
        FactProductionData,
        FactProductionData[Date] <= LASTDATE ( FactProductionData[Date] )
            && FactProductionData[Date]
                >= DATEADD(FactProductionData[Date], -12,DAY)
    )
)

[Zubair_Muhammad]

Hi @arifulice

Another way

=
CALCULATE (
    AVERAGE ( FactProductionData[Egg Prod#Qty] ),
    DATESINPERIOD (
        FactProductionData[Date],
        MAX ( FactProductionData[Date] ) - 11,
        12,
        DAY
    )
)

[arifulice]

Hi @Zubair_Muhammad

After applying your DAX ,when i dropped the measure in pivot table the following error message is showing attached in photo

EggProduction:=CALCULATE (
    AVERAGE ( FactProductionData[Egg Prod#Qty] ),
    DATESINPERIOD (
        FactProductionData[Date],
        MAX ( FactProductionData[Date] ) - 11,
        12,
        DAY
    )
)

[Zubair_Muhammad]

Hi @arifulice

This formula works fine with me.

see the attached file

[arifulice]

Hi @Zubair_Muhammad

I want to see the value of last 12 days but the below link giving the result of all month.please see the attached helow