Shows last month when slicer choosing current month.

[Julaiha]

Hai Expert,

I need to build a report for finance which is they want to show total amount of sales. We have slicer year and month, for example i choose year 2023 and month May so the line graph below will automatically shows sales amount from January to May. Is any dax function to create above scenario.

[Ahmedx · Accepted solution]

SORRY FOR CONFUSION!
PLEASE TRY THIS

Shows last month when slicer choosing current month.pbix

[Ahmedx · Accepted solution]

pls try this

measure2 = 
VAR _Yearselect = MAX('dup calendar'[Year])
VAR _MonthSelect = MAX('dup calendar'[Month Number])
RETURN
CALCULATE(
          [Sales] , KEEPFILTERS(FILTER(ALL('Calendar'),
                  'Calendar'[Year]= _Yearselect&&'Calendar'[Month Number]=_MonthSelect)))

[Ahmedx · Accepted solution]

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

[VilmarSch]

Veja se o PBIX aqui ajuda

[Ashish_Mathur]

Hi,

I have solved a similar problem in the attached PBI file.

[Julaiha]

I want to shows in Month, do I need to change DATEBETWEEN and date

[Ahmedx]

you need to disable the link between the calendar and the spreadsheet next
this is how you can read

measure =
VAR _Dateselect = MAX('Calendar'[Date])
VAR _YearSelect = YEAR(_Dateselect)
RETURN
CALCULATE(
          [Sales] , KEEPFILTERS(FILTER(ALL('table'),
                  'table'[Date]>= DATE(_YearSelect,1,1)&&'table'[Date]<=_Dateselect)))

[Julaiha]

Hai tq for response, I want to show in Month, I need to change YearSelect to MonthSelect right?

[Ahmedx · Accepted solution]

SORRY FOR CONFUSION!
PLEASE TRY THIS

Shows last month when slicer choosing current month.pbix

[Julaiha]

Hai Expert,

I managed to do as follow your dashboard guidance, as shown below;

Another question, may I know is it possible from the same slicer, the yellow KPI just shown value for March only and the graph shown for last month as above 

[Ahmedx · Accepted solution]

pls try this

measure2 = 
VAR _Yearselect = MAX('dup calendar'[Year])
VAR _MonthSelect = MAX('dup calendar'[Month Number])
RETURN
CALCULATE(
          [Sales] , KEEPFILTERS(FILTER(ALL('Calendar'),
                  'Calendar'[Year]= _Yearselect&&'Calendar'[Month Number]=_MonthSelect)))

[Julaiha]

Tq so much expert, u help me a lot 😊

[Ahmedx · Accepted solution]

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

[Julaiha]

Hai Expert, May I know why we need to duplicate the calendar table?

[Ahmedx · Accepted solution]

If a slicer is filtering one month, there is no way to show additional months from the same table on either the rows or the columns
Therefore, we must create a separate table that is not subject to filtering from the slicer. This way, columns from that table show all the rows, and we can control their visibility through a measure.