Last Week Sales from a Financial Calendar in a Card

[gavineoconnor]

Hi, I've been using Power Bi and the amazing forums to get answers when I get stuck for serveral months, but have stuggled to find an exact answer to my latest problem.

We use a Financial calendar that I've got as a table with Date, FinancialYear and FinancialWeek, which is linked ot the Sales table with Invoived date and Sales, our week runs Sunday to Saturday.

I've got last week's Sales Sum using:

LW = SUMX(

FILTER(ALL('Date'),

IF(SELECTEDVALUE('Date'[FiscalYear])=1,

'Date'[Fiscal_Week_of_Year]=CALCULATE(MAX('Date'[Fiscal_Week_of_Year]), ALL('Date')) && 'Date'[FiscalYear]=VALUE(SELECTEDVALUE('Date'[FiscalYear])-1),

'Date'[Fiscal_Week_of_Year]=SELECTEDVALUE('Date'[Fiscal_Week_of_Year])-1 && 'Date'[FiscalYear]=VALUE(SELECTEDVALUE('Date'[FiscalYear])))

),

[Sales Sum])

I want to show the last finanical week Sales Sum in a card or as a single cell in a matrix, but it just shows a blank!? Is this something to do with my FiscalCalendar going until the end of the Year but my invoiced data only as new as yesterday!?

I've attached a sample PBIX, hopefuly someone can help?

https://www.dropbox.com/s/rpfm0qzqvfeze0h/Date%20DAX.pbix?dl=0

The forum has been my main source of learning, thanks again to all contributors.

Gavin

[v-yuta-msft]

Hi gavineoconnor,

There're two conditions like:

'Date'[Fiscal_Week_of_Year]=SELECTEDVALUE('Date'[Fiscal_Week_of_Year])-1 && 'Date'[FiscalYear]=VALUE(SELECTEDVALUE('Date'[FiscalYear]))

So if you create two slicers and select values in your two slicers which may cause no one row can meet this condition, you may modify your conidtion of delete one of the condition in the measure like below:

LW = SUMX(
    FILTER(ALL('Date'),
        IF(SELECTEDVALUE('Date'[FiscalYear])=1,
           'Date'[Fiscal_Week_of_Year]=CALCULATE(MAX('Date'[Fiscal_Week_of_Year]), ALL('Date')) && 'Date'[FiscalYear]=VALUE(SELECTEDVALUE('Date'[FiscalYear])-1),
            'Date'[FiscalYear]=VALUE(SELECTEDVALUE('Date'[FiscalYear])))
    ),
    [Sales Sum])

 

Regards,

Jimmy Tao

[gavineoconnor]

Hi Jimmy, 

Thanks for the prompt reply, but this isn't giving me the correct answer. I'm looking for Last Week This Finacial Year's Sales to be in the card, which is "1220". Also i want this figure to display without having to create slicers, as i'm looking to out other measures on this page.

Thanks Gavin

[v-yuta-msft]

@gavineoconnor,

Have you solved your issue by now? If you have, could you please help mark the correct answer? Your contribution will be much appreciated.

Regards,

Jimmy Tao

[gavineoconnor]

Hi Jimmy, sorry for the delay, been away for the holidays, sorry but this didn't work, have i done something wrong?

Thanks,

Gavin 

[v-yuta-msft]

@gavineoconnor,

The earlier function should  be used in calculate column, please change the measure with calculate column and try again.

Regards,

Jimmy Tao

[gavineoconnor]

Hi Jimmy, i changed the first formula you provided into a column, but get the same error. I've updated my PBIX file with those calculations if that helps to look there? Thanks Gavin 

[v-yuta-msft]

@gavineoconnor,

Try modifying your measure like below:

Result =
CALCULATE (
    SUM ( table[value] ),
    FILTER (
        ALL ( table ),
        COUNTROWS (
            FILTER (
                table,
                table[week] = MAX ( table[week] )
                    && table[year] = EARLIER ( table[year] )
            )
        )
    )
)

Regards,

Jimmy Tao

[gavineoconnor]

Hi Jimmy, thanks but that as a measure that has returned total sales for FY17 and as a calculated column total sales for FY17 -1 week. DAX file updated in the original link. Thanks,

[v-yuta-msft]

Hi gavineoconnor,

You may create an addtional column [week] in a year, and use that column to filter the table using DAX format like pattern below:

Result =
CALCULATE (
    SUM ( table[value] ),
    FILTER (
        table,
        table[week] = MAX ( table[week] )
            && table[year] = EARLIER ( table[year] )
    )
)

Regards,

Jimmy Tao