Sales of december last year

[jpereztang]

Hi everyone,

i'm getting into trouble and I dont know how to solve. I would like to show the sales of December of last year but according to the period that is being shown as in the image.

Measure i want is SalesClosedYear. I hope tou can help me. Thanks

[tringuyenminh92]

Hi @jpereztang,

In case Period is Whole Number type, you could create calculated measure with expression:

SalesClosedYear = 
Var closed = (QUOTIENT(MAX(Sales[Period]),100)-1)*100 + 12
return CALCULATE(SUM(Sales[Sales]),FILTER(ALLSELECTED(Sales[Period]), Sales[Period]=closed ))

If this works for you please accept it as solution and also like to give KUDOS.

Best regards
Tri Nguyen

[Ashish_Mathur]

Hi @jpereztang,

Share the link from where i can download your file.

[GilbertQ]

Hi @Ashish_Mathur

I will send it tomorrow as I quickly did it as part of another Power BI file that I was working on.

There should be enough details for you to try it and see if you can get it working?

[GilbertQ]

Hi @jpereztang

The first thing that you would need to do is to create a Date Table. This is essential for you to use the Time Intelligence features in Power BI.

Here is my blog post on how to create a Date table.

https://www.fourmoo.com/2016/09/13/power-bi-how-to-easily-create-dynamic-date-tabledimension-with-fi...

Which you will then create a relationship to your table.

Next you would then create a measure in which you would get the value for the previous December. And then within the measure ensure that it repeats across all the Rows.

Here are the measures that I created, when I am developing I prefer to have seperate measures which makes troubleshooting and the entire process more granular. But makes sure that it is working as expected.

Sales = SUM(Data[Amount])

Sales Dec = CALCULATE([Sales],'Date'[Calendar Month] = "Dec")

Previous Dec Sales = 
CALCULATE (
    [Sales Dec],
         PARALLELPERIOD ( 'Date'[Date], -1, YEAR )
		  )

And here is the result, which is what you indicated with your image below.

[jpereztang]

Hi @GilbertQ I had already created a date table. I want say that  my date table only has one record per month because we take a picture of sales every month close I dont know if it still working as DateTable or is neccesary to have all dates of the year.

@GilbertQ I tried your solution and this is the result.

PD: I have tabular model connected to excel. 

[v-huizhn-msft]

Hi @jpereztang,

Have your resolved your problem based on @GilbertQ's solution? And I am confused about sentence " I want say that  my date table only has one record per month because we take a picture of sales every month close I dont know if it still working as DateTable or is neccesary to have all dates of the year."?

And from your first screenshot, there is 2015/12 data, where there is no corresponding in your result(the second screenshot)?

Best Regards,
Angelia

[GilbertQ]

Hi @jpereztang

In order for the time intelligence to work it will need dates for every day of each month even if there is no data. This will then ensure that the time intelligence funcation such as PARALLELPERIOD will work as expected.