0

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?

Faiz
  • 273
  • 2
  • 7
  • 21

1 Answers1

0

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.

Nelson Gomes Matias
  • 1,787
  • 5
  • 22
  • 35
  • Nelson - I forgot to mention (new to Power BI which is probably why) I am using a `clustered column chart` in which the axis is a `Date Hierarchy` and the value being the `Revenue` At week level it looks at the week as Monday-Sunday and not Sunday-Saturday which is what I am after – Faiz Jul 25 '17 at 08:24