Fiscal Year and Cumulative Sale

[Jcho10]

Hi everybody,

I would like to create this table in power bi but I have a few difficulties with “Fiscal Month” and “Cumulate Sales” :

 

I’ve created differents measures and 1 dimension :

DIMENSION :

MonthName (Text Format) :

This dimension is created with a TableDate

MEASURES :

Sales by month Year N :

VAR

YearN = max('Parametres Year'[YearN])

RETURN

CALCULATE(

sum('SalesTable'[Sales]);

'SalesTable'[Year]=YearN;

)

Sales by month Year N-1 :

VAR

YearN-1 = max('Parametres Year'[YearN_1])

RETURN

CALCULATE(

sum('SalesTable'[Sales]);

'SalesTable'[Year]=YearN-1;

)

Cumulate Sale Year N :

CALCULATE(

[Sales by month Year N];

DATESYTD('TableDate'[Date];"31/05")

)

Cumulate Sale Year N-1 :

CALCULATE(

[Sales by month Year N_1];

DATESYTD('TableDate'[Date];"31/05")

)

And I get this table :

 

Could you please help me ?

                -How could I sort my dimension in Fiscal Month order ?

The Fiscal year start in 06/01 and finish in 05/31.

                -How could I calculate “Cumulate Sale Year N-1” with “MonthName” ?

Thank you for your help

Sincerely

[v-shex-msft · Accepted solution]

Hi @Jcho10,

According to your description, you want to get the current sales, running current sales, previous sales, running previous sales, right?

If as I said, you can refer to below sample:

Table:

Measures:

Current Sales =
var currentDate=MAX(SalesTable[Date])
return
SUMX(FILTER(all(SalesTable),MONTH(SalesTable[Date])=MONTH(currentDate)&& YEAR(SalesTable[Date])=YEAR(currentDate)),[SalesAmount])

Previous Sales =
var currentDate=MAX(SalesTable[Date])
return
SUMX(FILTER(ALL(SalesTable),MONTH(SalesTable[Date])=MONTH(currentDate)&& YEAR(SalesTable[Date])=YEAR(currentDate)-1),[SalesAmount])

Running Current =
var currentDate=MAX(SalesTable[Date])
return
SUMX(FILTER(ALL(SalesTable),MONTH(SalesTable[Date])<=MONTH(currentDate)&&YEAR(SalesTable[Date])=YEAR(currentDate)),[SalesAmount])

Running Previous =
var currentDate=MAX(SalesTable[Date])
return
SUMX(FILTER(ALL(SalesTable),MONTH(SalesTable[Date])<=MONTH(currentDate)&&YEAR(SalesTable[Date])=YEAR(currentDate)-1),[SalesAmount])

Create the visuals.

Matrix:

Slicer:

Results:

If above is not help, please share us some sample data.

Regards,

Xiaoxin Sheng