Forum Discussion
Helper IQuarter calculation upto selected month
Hello,
I want a bar chart to reflect for Quarter in this way.
If I select jan in Q1 jan should appear ,feb selected - feb value should appear in Q1 ,mar selected then mar value should be there in Q1 .When apr is selected then in Q1 mar value should appear and Q2 apr value should appear ,Similar if may selected then mar value in Q1 and may value in Q2 etc...
I am uanble to achieve it ,I have created a replica of main Calendar table in backend and created a relationship like this
Also,in chart I am using Quarter from Previous Date table and in slicer using Year and month from main Calendar table.
I have used something like this in my dax
month_show_test =
VAR max_date =MAX ('Calendar'[%DATE_ID] )
RETURN
CALCULATE (
SUM ( Table[Value]] ),
TREATAS({max_date},'Previous Date'[%DATE_ID])) but no luck can anyone help how to achieve it
First make sure that your date table has a date column which uniquely identifies the year and month. In my example I am using 'Date'[Start of month], but you could equally use end of the month. The key point is that it is of type date, so that MAX will work correctly.
Create a disconnected copy of the date table. It should not have a relationship to any other tables, it is only for use in the slicer. In my example I call the table 'For Slicer'.
Use 'Date'[Year] and 'For Slicer'[Month name] in separate slicers. In your chart visual, use 'Date'[Quarter].
Create a measure like
My Measure = VAR MonthInSlicer = CALCULATE( MAX( 'For Slicer'[Start Of Month] ), TREATAS( VALUES( 'Date'[Calendar Year Number] ), 'For Slicer'[Calendar Year Number] ) ) VAR DateToUse = CALCULATE( MAX( 'Date'[Start Of Month] ), KEEPFILTERS( 'Date'[Start Of Month] <= MonthInSlicer ) ) VAR Result = CALCULATE( [Base Measure], 'Date'[Start Of Month] = DateToUse ) RETURN Resultwhere [Base Measure] is whatever you are trying to compute. You could also turn this code into a calculation item, and call SELECTEDMEASURE() instead of [Base Measure].
You need to use a disconnected table or the other quarters will not be v isible. Filters coming from a related table or from the same table will show only the rows that's been selected so if you select August, you will see Q3 only,.
13 Replies
johnt75Super User
First make sure that your date table has a date column which uniquely identifies the year and month. In my example I am using 'Date'[Start of month], but you could equally use end of the month. The key point is that it is of type date, so that MAX will work correctly.
Create a disconnected copy of the date table. It should not have a relationship to any other tables, it is only for use in the slicer. In my example I call the table 'For Slicer'.
Use 'Date'[Year] and 'For Slicer'[Month name] in separate slicers. In your chart visual, use 'Date'[Quarter].
Create a measure like
My Measure = VAR MonthInSlicer = CALCULATE( MAX( 'For Slicer'[Start Of Month] ), TREATAS( VALUES( 'Date'[Calendar Year Number] ), 'For Slicer'[Calendar Year Number] ) ) VAR DateToUse = CALCULATE( MAX( 'Date'[Start Of Month] ), KEEPFILTERS( 'Date'[Start Of Month] <= MonthInSlicer ) ) VAR Result = CALCULATE( [Base Measure], 'Date'[Start Of Month] = DateToUse ) RETURN Resultwhere [Base Measure] is whatever you are trying to compute. You could also turn this code into a calculation item, and call SELECTEDMEASURE() instead of [Base Measure].
KobesAdvocate I
Hi Harika, if I understand you well, you are trying to show all months in a quarter that have either started or are fully in the past, but not months from any quarter that are part of the future.
On way you may be able to achieve this is by adding a 'helper' column in the Calendar called "MonthsFromNow', it will contain a 0 for the current month, a negative value for past months and a positive value for the future months. The DAX looks like this for that column:
MonthsFromNow = DATEDIFF(TODAY(), 'Calendar'[%DATE_ID), MONTH )
With this column, you can then do:
Sum of Table[Value] :=
CALCULATE (
SUM (Table[Value] ),
Calendar[MonthsFromNow] <= 0
)If you want the limit of data to show being related to today, your best friend is likely to be DATESYTD and passing that as a filter table to your calculate reference:
Sum of Table[Value] YTD :=
VAR _DatesYTD = CALCULATETABLE (
DATESYTD( 'Calendar'[%DATE_ID] ),
'Calendar'[%DATE_ID] <= TODAY()
)
RETURN
CALCULATE(
SUM (Table[Value] ),
_DatesYTD
)- Kobes
The 'measure' you mention that is not working is actually a calculated column in DAX. It works if you use it as a column definition in your date table.
But I have a hard time understanding your business requirements. I am a but confusiod what your goal is with showing a visual based on Quarters, but then showing only a single month in all quarters. Isn't that just displaying a month-over-month visual?
- v-karpurapud
Community Support
Hi Harika05
Thank you for submitting your question to the Microsoft Fabric Community Forum, and thanks to Kobes , johnt75 and danextian for sharing helpful suggestions.
Could you let us know if the suggested solutions resolved your issue? If not, please share any additional details so we can assist further.
Best regards,
Community Support Team. Hi Harika05
May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.
Thank you
