Last month each year calculation

[Anonymous]

Hi,

I have a data set with lets say Sales Rate. This rate is different each month. I want to build a bar chart where the sales rate for the last month, Dec is shown for each year. And for this year the last month of data is shown, Sept in our case. 

2019=.89

2018= .83

2017=.80

2016=.80

MonthName-Year	PlacementRate
Sep-19	0.89%
Sep-18	0.92%
Sep-17	0.94%
Sep-16	0.83%
Oct-18	0.90%
Oct-17	0.92%
Oct-16	0.85%
Nov-18	0.85%
Nov-17	0.85%
Nov-16	0.83%
May-19	0.87%
May-18	0.88%
May-17	0.87%
May-16	0.80%
Mar-19	0.88%
Mar-18	0.89%
Mar-17	0.00%
Mar-16	0.78%
Jun-19	0.87%
Jun-18	0.89%
Jun-17	0.90%
Jun-16	0.82%
Jul-19	0.88%
Jul-18	0.90%
Jul-17	0.92%
Jul-16	0.84%
Jan-19	0.44%
Jan-18	0.36%
Jan-17	0.00%
Jan-16	0.80%
Feb-19	0.48%
Feb-18	0.41%
Feb-17	0.00%
Feb-16	0.77%
Dec-18	0.83%
Dec-17	0.80%
Dec-16	0.80%
Aug-19	0.89%
Aug-18	0.90%
Aug-17	0.93%
Aug-16	0.88%
Apr-19	0.86%
Apr-18	0.88%
Apr-17	0.85%
Apr-16	0.80%

[Icey]

Hi @Anonymous ,

Is this problem solved?

Best Regards,

Icey

[Anonymous]

No I haven't solved it yet, the MonthYear is a date field that I've converted in PowerBI 

[Icey]

Hi @Anonymous ,

If you don't mind, please share me a dummy PBIX file without real data and sensitive information.

Best Regards,

Icey

[amitchandak]

Try like

CALCULATE(sum(Sales[Sales Amount]),filter(sales,(year(Sales[Sales Date])<year(TODAY())&& MONTH(Sales[Sales Date])=12) || (year(Sales[Sales Date])=year(TODAY()) && MONTH(Sales[Sales Date])=MONTH(TODAY()) )))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Anonymous]

@amitchandak 

Thanks ! 

I tried this and I'm getting 0's with the formula below: 

Placement2 = CALCULATE(([PlacementRate]),FILTER(DIM_Date,(YEAR(DIM_Date[MonthName-Year])<YEAR(TODAY()) && MONTH(DIM_Date[MonthName-Year])=12 || (YEAR(DIM_Date[MonthName-Year])=YEAR((TODAY()) && MONTH(DIM_Date[MonthName-Year])=MONTH((TODAY()) ))))))

[amitchandak]

Is MonthName-Year is a date? My formula is based on date column

[amitchandak]

Also can control date from fact

Last month data = 
var _last_month =MONTH(maxx(all(Sales),Sales[Sales Date]))
return
CALCULATE(sum(Sales[Sales Amount]),filter(sales,(year(Sales[Sales Date])<year(TODAY())&& MONTH(Sales[Sales Date])=12) || (year(Sales[Sales Date])=year(TODAY()) && MONTH(Sales[Sales Date])=_last_month )))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601