Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
I need to create a bar chart, but I just want to show you a few months.
These months depend on the selection made on the slicer.
The calculation of the value of each month has the particularity mentioned in the image.
I really don't now how to do it.
I appreciate your help.
Solved! Go to Solution.
Hi @Anonymous ,
What I did was to create a new calendar table to use has slicer then added the following measure:
Filtering_Dates =
VAR Current_Month =
FILTER (
'Calendar',
'Calendar'[Monthyear] = SELECTEDVALUE ( 'Calendar_Filter'[Monthyear] )
)
VAR previous_Year =
FILTER (
'Calendar',
'Calendar'[Year] = YEAR ( MAX ( 'Calendar_Filter'[Date] ) )
&& MONTH ( 'Calendar'[Date] ) IN { 1, 12 }
)
VAR January_December_Selection =
FILTER (
'Calendar',
'Calendar'[Year] = MAX ( 'Calendar_Filter'[Year] )
&& MONTH ( 'Calendar'[Date] )
IN { 1 }
|| (
'Calendar'[Year]
= MAX ( 'Calendar_Filter'[Year] ) - 1
&& MONTH ( 'Calendar'[Date] ) IN { 12 }
)
)
VAR Previous_Month =
FILTER (
'Calendar',
'Calendar'[Year] = MAX ( 'Calendar_Filter'[Year] )
&& MONTH ( 'Calendar'[Date] )
= MONTH ( MAX ( Calendar_Filter[Date] ) ) - 1
)
VAR FinalFilter =
IF (
YEAR ( MAX ( Calendar_Filter[Date] ) ) <> YEAR ( TODAY () ),
COUNTROWS ( UNION ( Current_Month, previous_Year) ),
IF (
MONTH ( MAX ( Calendar_Filter[Date] ) ) <= 2,
COUNTROWS ( UNION ( Current_Month, January_December_Selection ) ),
COUNTROWS ( UNION ( Current_Month, January_December_Selection, Previous_Month ) )
)
)
RETURN
FinalFilter
Result below and in attach file:
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Anonymous ,
Please provide a sample .pbix file.
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Altough you don't present any data setup I assume you have a calendar table and you data has a start and end date, also don't know if you have the table related in either case you need to have a calculation based on a disconnected table this can be done in two ways.
With Disconnected date table
Add the following measure:
HEADCOUNT =
CALCULATE (
Table[ID],
FILTER (
ALLSELECTED ( Table[ID], Table[Start Date], Table[End Date] ),
Table[Start Date] <= MIN ( Calendar[Date] )
&& Table[End Date] >= MAX ( Calendar[Date] )
)
)
Related Tables (Calendar[DATE] and TRable[StartDate]
Create the following measure:
HEADCount =
CALCULATE (
Table[ID],
FILTER (
ALLSELECTED ( Table[ID], Table[Start Date], Table[End Date] ),
Table[StartDate] <= MIN ( Calendar[Date] )
&& Table[End Date] >= MAX ( Calendar[Date] )
),
CROSSFILTER ( Calendar[Date], Table[StartDate], NONE )
)
On the X-axis and in the slicer use the Calendar table column.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix ,
Thanks for the help, but I probably didn't make myself understood.
I want to select a date in the slicer and I want some month valuesalways appear, for example:
Imagine that I'm selecting March 2020.
I want to get the values for January and August 2019 and also the value for the month before the one selected.
Thanks
What are the months you want to show? Is the selected month, previous months and the others are random or fixed?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix
If selected Year <> Current Year then,
- show Jan and Dez previous Year
If selected Year = Current Year and Actual Month Jan
- show Jan and Dez previous Year
If selected Year = Current Year and Actual Month = Fev
- show Jan current year and Jan and Dez previous year
If selected Year = Current Year and Actual Month > Fev
- show Prev current month and Jan current Year and Jan and Dez previous year
I have already implemented a solution with a disconnected table but the problem is how the values are calculated.
The calculation of the month value is represented by the lines that are not in that month.
For example: the number of employees for January is the country of all names whose admission date is <1 January and ending contract > 31 January.
In a table and for a single month it works.
I have already thought of using a calendar table with Offset months.
What do you think?
Regards
Joao Monteiro
Hi @Anonymous ,
What I did was to create a new calendar table to use has slicer then added the following measure:
Filtering_Dates =
VAR Current_Month =
FILTER (
'Calendar',
'Calendar'[Monthyear] = SELECTEDVALUE ( 'Calendar_Filter'[Monthyear] )
)
VAR previous_Year =
FILTER (
'Calendar',
'Calendar'[Year] = YEAR ( MAX ( 'Calendar_Filter'[Date] ) )
&& MONTH ( 'Calendar'[Date] ) IN { 1, 12 }
)
VAR January_December_Selection =
FILTER (
'Calendar',
'Calendar'[Year] = MAX ( 'Calendar_Filter'[Year] )
&& MONTH ( 'Calendar'[Date] )
IN { 1 }
|| (
'Calendar'[Year]
= MAX ( 'Calendar_Filter'[Year] ) - 1
&& MONTH ( 'Calendar'[Date] ) IN { 12 }
)
)
VAR Previous_Month =
FILTER (
'Calendar',
'Calendar'[Year] = MAX ( 'Calendar_Filter'[Year] )
&& MONTH ( 'Calendar'[Date] )
= MONTH ( MAX ( Calendar_Filter[Date] ) ) - 1
)
VAR FinalFilter =
IF (
YEAR ( MAX ( Calendar_Filter[Date] ) ) <> YEAR ( TODAY () ),
COUNTROWS ( UNION ( Current_Month, previous_Year) ),
IF (
MONTH ( MAX ( Calendar_Filter[Date] ) ) <= 2,
COUNTROWS ( UNION ( Current_Month, January_December_Selection ) ),
COUNTROWS ( UNION ( Current_Month, January_December_Selection, Previous_Month ) )
)
)
RETURN
FinalFilter
Result below and in attach file:
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |