Monthly sales - calendar day issue

[AndreaBac]

Hi all

Im a new user of PowerBI Desktop, and faced an issue.

I need to create a monhtly basis report.

1. I have sales tables includes YYYYMM column
2. I have calendar day table which includes daily and marked as date table by  the day column (2020.12.30)
3. I have Date table which in monthly basis, Year, Month, YYYYMM, starting day

The starting day from date table has a relationship with day column in calendar day table

YYYYMM from DATE table has relationship with sales tables YYYYMM column

Made some measure: for example:

Sales USD= calculate(sum('SALES 1'[Amount USD]),filter('SALES 1','SALES 1[PnL row]="NSV"), filter('SALES 1', 'SALES 1'[Type of data]="ACTUAL"))/1000

SALES USD Last year= calculate([SALES USD],SAMEPERIODLASTYEAR('CALENDAR DAY'[DAY]))

When I select 1 month and 1 year both measure is perfect.. but if Im selecting more month the Last Year measure doesnt show anything

Where the issue should be?

[aj1973 · Accepted solution]

Hi @AndreaBac 

You are using a Time intelligence formula SAMEPERIODLASTYEAR towards the Calendar Day table. for the formula to be accurate needs to use a column where there is MMDDYYYY. so the relationships between your Tables needs to be changed to columns where there is YYYYMMDD like in your Model.

[AndreaBac]

Thanks, that solved my issue!

[Ashish_Mathur · Accepted solution]

Hi,

You should not have a Date Table and a Calendar day Table.  Just have a Calendar table with running dates.  Using calculated column formulas, you must have Year, Month Name and Month number columns as well.  Sort the Month Name by the Month number column.  Create a relationship from the Date column of your Sales table to the Date column of your Calendar Table.  To your visual/filter/slicer, drag any and all date dimensions only from the Calendar Table.  Select any one Year and Month Name.  Your measures can be simplified to:

Sales USD= calculate(sum('SALES 1'[Amount USD]),'SALES 1[PnL row]="NSV",'SALES 1'[Type of data]="ACTUAL")/1000

SALES USD Last year= calculate([SALES USD],SAMEPERIODLASTYEAR('CALENDAR DAY'[Date]))

Hope this helps.

[aj1973 · Accepted solution]

Hi @AndreaBac 

You are using a Time intelligence formula SAMEPERIODLASTYEAR towards the Calendar Day table. for the formula to be accurate needs to use a column where there is MMDDYYYY. so the relationships between your Tables needs to be changed to columns where there is YYYYMMDD like in your Model.