1

I have an issue I wish I would get any advice from you. I have 2 table that relates each other which are fact_table and calendar_table. I would create a simple column chart where MonthName (from calendar) as X-Axis and ValueColumn (from fact_table) as Y-axis. Then I create a slicer that refer to MonthName column (same as X-Axis). I expect when I select Slicer (example, April), then the chart will display values of Jan-April and leave May-Dec blank (with X axis values fixed; Jan-Dec, eventho blank).

Would you please assist me what DAX measure I should create to fulfill above requirement. Thank you

sample data as follows where both relates to each other through column 'Date'. And for both slicer and X axis refer to same column: Calendar[MonthName].

Table Calendar

Date MonthName MonthNumber Year
1-1-2022 January 1 2022
1-2-2022 February 2 2022

Table Fact

Date Category Value
1-1-2022 Actual 1000
1-2-2022 Estimate 1200
Nur Holis
  • 31
  • 1
  • 6

1 Answers1

1

You can try a measure as below-

measure = 

var max_date = Max(Calendar[Date])
var max_year = Max(Year(Calendar[Date]))

RETURN
SWITCH(
    ISFILTERED (Calendar[Date]),
    TRUE(),
    CALCULATE(
        SUM(fact_table[column_name]),
        FILTER(
            ALL(Calendar),
            Year(Calendar[Date]) = max_year
                && Calendar[Date] <= max_date
        )
    ),
    SUM(fact_table[column_name])
)
mkRabbani
  • 16,295
  • 2
  • 15
  • 24
  • Hi @mkRabbani - did you test this solution? This measure looks like a cumulative sum and not what I think OP is asking for. – Davide Bacci Dec 30 '22 at 09:01
  • I just shared an idea! Without any sample data and output required. And this will return a Year to Month value for the selected month in the filter. Please correct me if I am worng. – mkRabbani Dec 30 '22 at 11:26
  • OP does need to be clearer with their requirements as we have both interpreted them differently. Let's see if they update. – Davide Bacci Dec 30 '22 at 11:44
  • Hi @mkRabbani, thank you for your reply. I do apologize for unclear question. I have just edited the question by adding sample tables. Actually in fact_table, there are category: Actual and Estimate. I want, when I select Slicer 'April', the chart will display actual from Jan-April, but in same time and chart will display Estimate value in May-Dec. ***Note: each month has both values: actual & estimate – Nur Holis Dec 31 '22 at 01:27