Averagex based on dates

Question

Hi.&nbsp;Can somebody please help me correct this DAX equation? I want to know the average days of inventory based on dates. For visual reasons I have restricted it to 12 months but I want to know the accumulated average days. I am missing something in the DAX because the average days has to be between 400-300 days 🙂&nbsp;Hope it makes sense.&nbsp;&nbsp;&nbsp;VAR _dato = MAX(Kalender[Date])RETURNAVERAGEX(&nbsp; &nbsp; CALCULATETABLE(&nbsp; &nbsp; &nbsp; &nbsp; Data,&nbsp; &nbsp; &nbsp; &nbsp; ALL(Kalender[Date]),&nbsp; &nbsp; &nbsp; &nbsp; Data[Registreret dato] &lt;_dato &amp;&amp; (Data[Afsluttet dato] &gt; _dato || ISBLANK(Data[Afsluttet dato]))&nbsp; &nbsp; ),&nbsp; &nbsp; DATEDIFF(Data[Registreret dato], _dato,DAY))&nbsp;Best regards,Lisa&nbsp;

johnt75 · Answer

I would try to examine the data returned by CALCULATETABLE for a particular date to make sure that it is returning what you expect it to. You can use DAX Studio to run the below query
DEFINE
    VAR DateFilter =
        TREATAS ( { DATE ( 2022, 6, 30 ) }, 'Kalendar'[Date] )

EVALUATE
CALCULATETABLE (
    VAR _dato =
        MAX ( Kalender[Date] )
    RETURN
        CALCULATETABLE (
            Data,
            ALL ( Kalender[Date] ),
            Data[Registreret dato] &lt; _dato
                &amp;&amp; (
                    Data[Afsluttet dato] &gt; _dato
                        || ISBLANK ( Data[Afsluttet dato] )
                )
        ),
    DateFilter
)

anonymous · Answer

johnt75&nbsp;I am not a super user of Power BI, so I dont know the DAX studio. Can I do something else?&nbsp;😬

johnt75 · Answer

You could create a temporary table within Power BI
Temp table =
VAR DateFilter =
    TREATAS ( { DATE ( 2022, 6, 30 ) }, 'Kalendar'[Date] )
RETURN
    CALCULATETABLE (
        VAR _dato =
            MAX ( Kalender[Date] )
        RETURN
            CALCULATETABLE (
                Data,
                ALL ( Kalender[Date] ),
                Data[Registreret dato] &lt; _dato
                    &amp;&amp; (
                        Data[Afsluttet dato] &gt; _dato
                            || ISBLANK ( Data[Afsluttet dato] )
                    )
            ),
        DateFilter
    )

You would then be able to examine the table in the data view and see if it contains what you are expecting

anonymous · Answer

johnt75&nbsp;Hi.I tested it out and the temp table did show the correct data however where do I go from here? I want to calculate the avarage days.&nbsp;&nbsp;

johnt75 · Answer

Your formula looks correct to me, the only thing I can think of is that some other filters are in place. When you restricted the visual to just 12 months did you use the Date table or the Data table? You need to use the Date table because that is what the code is removing filters from.
Is it possible to share a PBIX with sample data ?

Forum Discussion

Averagex based on dates

8 Replies

Related content

Averagex issue

Question on AVERAGEX

AverageX

Dynamic Filtering based on date selection

Averagex based on filter condition