Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Julaiha
Frequent Visitor

Shows last month when slicer choosing current month.

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.

4 ACCEPTED SOLUTIONS

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)))

Screenshot_1.png

View solution in original post

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.

View solution in original post

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.

View solution in original post

12 REPLIES 12
VilmarSch
Post Partisan
Post Partisan

Veja se o PBIX aqui ajuda

Ashish_Mathur
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Ahmedx
Super User
Super User

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)))

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

Hai Expert,

 

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

Julaiha_0-1692285907844.png

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 

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)))

Screenshot_1.png

Tq so much expert, u help me a lot 😊

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.

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

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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.