Calculate+Datesbeween in Line Graph

[cn4422]

Hi,

I have created the following measure, which I then implemented in a line graph. However, the measuer is only showing the total sum for every month, and not the real sum/month.

Any ideas on how to fix this?

Count Forms Category Test 2023 =

CALCULATE(COUNT('lead'[Forms category]),

ALL(),

'lead'[Country]="Test Form",

'lead'[Forms category]="Test Category",

DATESBETWEEN(Datum[Date],DATE(2023,1,1),DATE(2023,8,31)

))

When I'm not using "time" in my measure but a slicer, it's working fine (but I want to it work without date slicer).

The actual goal is to display two graphs, one for 2023 and one for 2024 in the same 12 months chart.

Thanks for your help!

[Anonymous · Accepted solution]

Hi @cn4422 

Try using VALUES(Datum[Date]. [Month]) in your measure to ensure that the data is summarized by month.

Count Forms Category Test 2023 by Month = 
CALCULATE(
    COUNT('lead'[Forms category]),
    'lead'[Country] = "Test Form",
    'lead'[Forms category] = "Test Category",
    DATESBETWEEN(Datum[Date], DATE(2023, 1, 1), DATE(2023, 12, 31)),
    VALUES(Datum[Date].[Month])
)

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[cn4422]

Thank you very much, that did the trick! 🤗

One more question, if I may:

With your help I've created this measure for 2024:

Count Forms Category DE 2024 TEst 2 plus date slicer =

CALCULATE(COUNT('lead'[Forms category]),

'lead'[Country]="Test Country",

'lead'[Forms category]="Test Form",

    VALUES(Datum[Date].[Monat]

))

This works just fine in the graph.

Then I added this measure for SPLY 2023:

Count Forms Category DE 2023 SPLY TEst 2 plus date slicer =

VAR dateRange = DATESBETWEEN('Datum'[Date], MIN(Datum[Date]),MAX(Datum[Date]))

Var dateRange2 = Sameperiodlastyear(dateRange)

RETURN

CALCULATE(COUNT('lead'[Forms category]),

'lead'[Country]="Test Country",

'lead'[Forms category]="Test form",

    ALL('Datum'),

dateRange2

)

Upon adding this measure to the graph, it shows all 12 months (from 2023) which I don't want --> It should only show the months as selected in the slicer.

Maybe the error lies somewhere in this line:
DATESBETWEEN('Datum'[Date], MIN(Datum[Date]),MAX(Datum[Date]))

...

Thanks again for your help!

[Anonymous]

Hi @cn4422 

The problem you are experiencing is due to the use of the ALL('Datum') function, which ignores all filters in the Datum table. Refer to link: ALL function (DAX) - DAX | Microsoft Learn.

Drop ALL(Datum) and try the following DAX:

Count Forms Category DE 2023 SPLY TEst 2 plus date slicer = 
CALCULATE(
    COUNT('lead'[Forms category]),
    'lead'[Country] = "Test Form",
    'lead'[Forms category] = "Test Category",
    SAMEPERIODLASTYEAR(Datum[Date])
)

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[cn4422]

Ah, sure... 🙈

Thank you so much, it's working fine! 👍

Kind regards,
Michael