Cannot display a measure on histogram and looking for an alternative

[qpnna]

Greetings, 

I have a table with record, and multiple What If parameters which allow me to add a number of month to the date of each record. 
Currently I am habing trouble to display the number of record per month while taking into account the new date. 
I have created a measure which calculate the new date, but unfortunately I cannot use it on a histogram. 

I've also tried to create a new column and table, but it failed to read the value of my parameters, let alone preserve the dynamic aspect of my parameters. 

Is there any alternative ? The best way would be counting directly into my measure but I can't see how since it's not a table. I've come full circle into that problem. Displaying the measure into a table shows me the correct date, which takes into account properly the parameters.

[v-sdhruv]

Hi @qpnna ,
I hope you were able to resolve the issue.
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[v-sdhruv]

Hi @qpnna ,
The functions like Addcolumns, Summarize usually are used when you create a table and hence the DAX provided should be used in calculated table then it will work.

I hope you were able to resolve the issue.
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[DataNinja777]

Hi @qpnna ,

You are facing a common issue where measures cannot be used as categorical fields in a histogram because they are evaluated in the context of visualizations and cannot generate a dynamic set of categorical values. Instead of trying to use your measure as a category, you can create a virtual table that counts records per month and then plot that as a column/bar chart. One way to do this is by creating a measure that calculates the adjusted dates dynamically and summarizes the record count per month.

RecordsPerMonth = 
VAR AdjustedDates = 
    ADDCOLUMNS(
        'YourTable', 
        "NewDate", DATEADD('YourTable'[Date], [YourWhatIfParameterValue], MONTH)
    )

VAR MonthCounts = 
    SUMMARIZE(
        AdjustedDates,
        FORMAT([NewDate], "YYYY-MM"),
        "Count", COUNTROWS(AdjustedDates)
    )

RETURN
    MonthCounts

This measure allows you to view the correct count of records per adjusted month in a table. If you need to use it in a bar chart, you can use "YYYY-MM" as the X-axis. However, since calculated columns do not react to What-If parameters, another approach is to create a separate calculated table that dynamically updates based on your parameter, though this requires a dataset refresh to reflect changes.

NewTable = 
ADDCOLUMNS(
    'YourTable',
    "NewDate", DATEADD('YourTable'[Date], [YourWhatIfParameterValue], MONTH)
)

If you need full interactivity without refreshing, using a supporting Date Table is the best solution. Ensure you have a Date Table and create a measure that counts records per month while treating the adjusted dates dynamically.

DateTable = ADDCOLUMNS(
    CALENDAR(MIN('YourTable'[Date]), MAX('YourTable'[Date])),
    "MonthYear", FORMAT([Date], "YYYY-MM")
)

CountRecords = 
VAR AdjustedDates = 
    ADDCOLUMNS(
        'YourTable', 
        "NewDate", DATEADD('YourTable'[Date], [YourWhatIfParameterValue], MONTH)
    )

RETURN
CALCULATE(
    COUNTROWS(AdjustedDates), 
    TREATAS(VALUES(DateTable[Date]), AdjustedDates[NewDate])
)

With this approach, you can use DateTable[MonthYear] as the X-axis and CountRecords as the measure in your histogram, ensuring that your What-If parameters dynamically affect the visualization. Let me know if you need any refinements!

Best regards,

[qpnna]

Thanks for your reply,

Regarding this code 

RecordsPerMonth = 
VAR AdjustedDates = 
    ADDCOLUMNS(
        'YourTable', 
        "NewDate", DATEADD('YourTable'[Date], [YourWhatIfParameterValue], MONTH)
    )

VAR MonthCounts = 
    SUMMARIZE(
        AdjustedDates,
        FORMAT([NewDate], "YYYY-MM"),
        "Count", COUNTROWS(AdjustedDates)
    )

RETURN
    MonthCounts

Is this an entire measure ? The VAR MonthCounts failed to read the "NewDate" added by ADDCOLUMNS, it sounds like it is supposed to be a table but is declared as a measure ?

[bhanu_gautam]

@qpnna You can try creating a calculated table that includes the adjusted dates and then use this table for your visualization:

DAX
AdjustedRecords =
ADDCOLUMNS(
Records,
"AdjustedDate", DATEADD(Records[OriginalDate], [MonthsToAdd], MONTH)
)

Then, create a measure to count records per month from this calculated table:

RecordsPerMonth =
COUNTROWS(
FILTER(
AdjustedRecords,
YEAR([AdjustedDate]) = YEAR(TODAY()) && MONTH([AdjustedDate]) = MONTH(TODAY())
)
)

Finally, use the RecordsPerMonth measure in your histogram or other visualizations to display the count of records per month.

[qpnna]

Thanks for your reply,

I've tried this method once, the problem is when whenever I pass by a new Table or Column, it failed to read the value of my What If parameters. It will always default it to 1, which is the MIN of my range of value.

Used alone in a measure will work properly, but once I try to create a Column or Table it will stop to work.