CODEWITHSUNDEEP
powerbidaxssas-tabular
Faiz
Faiz

Reputation: 283

Calculate revenue by week - sunday to saturday (last year) on tabular cube/Power BI

I have a report on Power BI where data comes from a tabular cube. The DAX expressions are made in the cube. When drilling down in Power BI and comparing results to the original numbers there is a slight difference. Power BI calculates the week figures as monday - sunday, but I want it to show from sunday - saturday

Initial code:

IF(ISBLANK([Revenue]), BLANK(), CALCULATE([Revenue],SAMEPERIODLASTYEAR('Date'[Date]),ALL('Date'[Date])))

The date table in cube contains columns [SortFiscalWeek], [SortFiscalMonth], [SortFiscalYear], [Week Commencing],Fiscal Week Number.

Week Commencing is in accordance to Sunday (being the week commence date) - Saturday.

The sort fiscal columns start as 1 from years ago and end to whichever number it is today based on today's date.

I added Fiscal Week Number as I thought it might be helpful and tried using it as Test New Users LY = IF(ISBLANK([New Users]),BLANK(),CALCULATE([New Users],SAMEPERIODLASTYEAR('Date'[Date]),FILTER(ALL('date'),'Date'[Fiscal Week Number]='Date'[Fiscal Week Number]&&'Date'[YearInt] = 'Date'[YearInt]-1)))

EDIT

In Power BI I am using a Clustered Column Chart with Date Hierarchy in the Axis and Revenue in the Value. On week level it sums the Revenue as Monday-Sunday and not Sunday-Saturday. Hope this clears up some doubt as to how i am using the data in Power BI

Can anyone help?

Upvotes: 0

Views: 821

Answers (1)

Nelson Gomes Matias
Nelson Gomes Matias

Reputation: 1997

If I understood well, I had the same problem, I created a calculated column giving me the name of the month, like this:

MonthName = SWITCH(DimDate[Month], 1,"gennaio",2,"febbraio",3,"marzo",4,"aprile",5,"maggio",6,"giugno",7,"luglio",8,"agosto",9,"settembre",10,"ottobre",11,"novembre",12,"dicembre","altro")

then I needed to order the month by month number, so to do this you need to select your calculated column, and then go to the tab Modelling>Sort by column, in this menu you can choose on which field of the table you order the selected field.

Hope that helps.

Upvotes: 0

Related Questions