DAX measure using dates uses too much memory

[Thefae]

Hi,

I'm fairly new to Power BI and have found so much help on this site thank you!

I have a matrix visual that works fine in Desktop view, but exceeds memory usage when I publish it to app.powerbi.com (Since I usually work in Desktop - I'm not sure in which group to put my question)

The visual shows the "campaign revenue" for a few products in each their specific time period (their "campaign period").
I have a supporting small excel file with the SKU, start date ('kampagnemaal'[Startdato]), end date('kampagnemaal'[Slutdato]).The dates are different for each product:

My measure for works fine in desktop, but is veeeery slow. I need specific tips for changing my data or DAX to get below 1024 mb ram. 

Current measure:

Omsætning kampagneperiode = CALCULATE(SUM('fact Værdiposter (Value Entry)'[Sales Amount (Actual)]),

FILTER('Date', 'Date'[Date]>min('kampagnemaal'[Startdato])),

FILTER( 'Date', 'Date'[Date]<min('kampagnemaal'[Slutdato]))

)

Since the dates are only for this year, I tried filtering my data ('fact Værdiposter (Value Entry)'[Sales Amount (Actual)]) in powerquery to only show this year, but that made no visible difference.

I found out that my problem might be that "filter" is not the right way to go, but can't figure out which way to go then? 🙂

Hopefully, you can help!

Thank you, 

Theresa

[m3tr01d]

Hi @Thefae,

how many years do you have in your Date table?

[Thefae]

Only 3 🙂  Fowmys re-writing of my measure worked 🙂

[Fowmy · Accepted solution]

@Thefae 

Can you try this version please:

Omsætning kampagneperiode =
var __Startdato  = MIN ( 'kampagnemaal'[Startdato] )
var __Slutdato = MIN ( 'kampagnemaal'[Slutdato] )
return
CALCULATE (
    SUM ( 'fact Værdiposter (Value Entry)'[Sales Amount (Actual)] ),
    FILTER ( 
        all('Date'), 
        'Date'[Date] > __Startdato && 'Date'[Date] < __Slutdato 
    )
)

[Thefae]

That worked perfectly, thank you!