Previous Month

[mnarmeen]

Hello BI users,

I have a problem which has many related posts but still couldnt get my chart to work. So any help would be appreciated

I have to show a value of total sales which is already calculated and stored in my table for each month(one value for every month of the year as 2017-05-01 for may and 2017-04-01 for april etc.). In BI, Current date(filterdate) which has a format yyyy-mm-dd is selected by the slicer

filterdate = format(DATE(erie_sale[YearD],erie_sale[MonthD],1),"yyyy-mm-dd")

and previous month and sales is calculated as below

PreviousMonth = PREVIOUSMONTH(erie_sale[filterdate])

PreviousSales = CALCULATE(SUM(erie_sale[Total]),PREVIOUSMONTH(erie_sale[filterdate]))

Uptil here the data shows fine in charts . but the problem is when I add month and year slicer extracted from the newdate (stored in sql as date type) it doesnot show me the correct data. It only shows me current month data the PreviousSales shows blank

MonthD = MONTH(erie_sale[newdate])

YearD = Year(erie_sale[newdate])

According to my understanding it shouls make a filter date from the month and year selected in slicer and 1 is passed expilictly as all dates are just the 1st of the month. then it should calculate previous month (which is working absolutely fine) and then measure PreviousSales should get calculated (which is not working in the case when only month and year is selected from the slicer ).

[v-huizhn-msft · Accepted solution]

Hi @mnarmeen,

After research and test, you should create a Calendar table, and create a relationship with your 'erie_sale' table by date column.

Then create a measure using the formula, then add it to your visual.

PreviousSales = CALCULATE(SUM(erie_sale[Total]),PREVIOUSMONTH(Calendar[date]))

Best Regards,
Angelia

[v-huizhn-msft · Accepted solution]

Hi @mnarmeen,

After research and test, you should create a Calendar table, and create a relationship with your 'erie_sale' table by date column.

Then create a measure using the formula, then add it to your visual.

PreviousSales = CALCULATE(SUM(erie_sale[Total]),PREVIOUSMONTH(Calendar[date]))

Best Regards,
Angelia

[mnarmeen]

@v-huizhn-msft

Thankyou so much for your answer. I have created a table dimdate and made a relation with my sales table. But the problem is im trying to show the sum of quantity stored in a different table (sale_enteries) which has relation with my sales table on the basis of invoice id.

Furthermore, date in sales table is a calculated column (based on some criteria).

Do you think your solution will work if I create relationship with a calculated column ?

dimdate(date) with sales(date which is a calculated column)

[v-huizhn-msft · Accepted solution]

Hi @mnarmeen,

I know your calculated column is [filterdate]. I think it still works, Please try and respond me if you have any issue.

Best Regards,
Angelia

[mnarmeen]

Thankyou for your reply.

So, what I have done so far is that I have created a dimdate in sql and populated it with dates and now im making a relation (1-M) with my date column which isnt working .

RelationDiagram

[v-huizhn-msft]

Hi @mnarmeen,

Do you mind share your .pbix file for further analysis?

Best Regards,
Angelia