Forum Discussion

JorgeAbiad's avatar
JorgeAbiad
Helper III
1 year ago
Solved

Help with new column using DAX

Hello PBI Experts,

I am having a problem with creating a column using DAX.

Kindly check what is wrong with below DAX:

License Limit =
VAR date0 = {DATEVALUE("April 2024"), DATEVALUE("May 2024"), DATEVALUE("June 2024"), DATEVALUE("July 2024"), DATEVALUE("August 2024"), DATEVALUE("September 2024")}
VAR date1 = {DATEVALUE("October 2024"), DATEVALUE("November 2024"), DATEVALUE("December 2024")}
VAR date2 = {DATEVALUE("January 2025"), DATEVALUE("February 2025"), DATEVALUE("March 2025"), DATEVALUE("April 2025"), DATEVALUE("May 2025"), DATEVALUE("June 2025"),DATEVALUE( "July 2025")}
RETURN
SWITCH(
    TRUE,
    'Consolidated_AS-ONE'[D365 Base]="D365 Sales Enterprise"  && 'Consolidated_AS-ONE'[Status Date].[Date] IN date0, 300,
    'Consolidated_AS-ONE'[D365 Base]="D365 Sales Enterprise"  && 'Consolidated_AS-ONE'[Status Date].[Date] IN date1, 500,
    'Consolidated_AS-ONE'[D365 Base]="D365 Sales Enterprise" && 'Consolidated_AS-ONE'[Status Date].[Date] IN date1, 600,
    'Consolidated_AS-ONE'[D365 Base]="D365 Team Member", 30,
    'Consolidated_AS-ONE'[D365 Base]="D365 Marketing", 100
)
In the above formula:
1. April to September 2024 should have a value of 300
2. October to Decemeber 2024 should have a value 0f 500
3. January to July 2025 is 600
For values of 30 and 100, there are no problems as I am getting the intended result. But for the first 3 conditions, the month of July is getting the 300 as values. Please see at the filtered table below:

All other values are blank.

 

BR,

Jorge 

 

Need Help
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    Hi JorgeAbiad - You need to remove the overlap by ensuring each date range is distinct. Also, correct the third condition to use date2 for January–July 2025.

     

    License Limit =
    VAR date0 = {DATEVALUE("April 2024"), DATEVALUE("May 2024"), DATEVALUE("June 2024"), DATEVALUE("July 2024"), DATEVALUE("August 2024"), DATEVALUE("September 2024")}
    VAR date1 = {DATEVALUE("October 2024"), DATEVALUE("November 2024"), DATEVALUE("December 2024")}
    VAR date2 = {DATEVALUE("January 2025"), DATEVALUE("February 2025"), DATEVALUE("March 2025"), DATEVALUE("April 2025"), DATEVALUE("May 2025"), DATEVALUE("June 2025"), DATEVALUE("July 2025")}
    RETURN
    SWITCH(
    TRUE,
    'Consolidated_AS-ONE'[D365 Base]="D365 Sales Enterprise" && 'Consolidated_AS-ONE'[Status Date].[Date] IN date0, 300,
    'Consolidated_AS-ONE'[D365 Base]="D365 Sales Enterprise" && 'Consolidated_AS-ONE'[Status Date].[Date] IN date1, 500,
    'Consolidated_AS-ONE'[D365 Base]="D365 Sales Enterprise" && 'Consolidated_AS-ONE'[Status Date].[Date] IN date2, 600,
    'Consolidated_AS-ONE'[D365 Base]="D365 Team Member", 30,
    'Consolidated_AS-ONE'[D365 Base]="D365 Marketing", 100
    )

    Now, July 2025 will get the correct value of 600, as it is only included in date2. This should resolve the issue of incorrect values for July. Hope this helps in your scenerio

1 Reply

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    Hi JorgeAbiad - You need to remove the overlap by ensuring each date range is distinct. Also, correct the third condition to use date2 for January–July 2025.

     

    License Limit =
    VAR date0 = {DATEVALUE("April 2024"), DATEVALUE("May 2024"), DATEVALUE("June 2024"), DATEVALUE("July 2024"), DATEVALUE("August 2024"), DATEVALUE("September 2024")}
    VAR date1 = {DATEVALUE("October 2024"), DATEVALUE("November 2024"), DATEVALUE("December 2024")}
    VAR date2 = {DATEVALUE("January 2025"), DATEVALUE("February 2025"), DATEVALUE("March 2025"), DATEVALUE("April 2025"), DATEVALUE("May 2025"), DATEVALUE("June 2025"), DATEVALUE("July 2025")}
    RETURN
    SWITCH(
    TRUE,
    'Consolidated_AS-ONE'[D365 Base]="D365 Sales Enterprise" && 'Consolidated_AS-ONE'[Status Date].[Date] IN date0, 300,
    'Consolidated_AS-ONE'[D365 Base]="D365 Sales Enterprise" && 'Consolidated_AS-ONE'[Status Date].[Date] IN date1, 500,
    'Consolidated_AS-ONE'[D365 Base]="D365 Sales Enterprise" && 'Consolidated_AS-ONE'[Status Date].[Date] IN date2, 600,
    'Consolidated_AS-ONE'[D365 Base]="D365 Team Member", 30,
    'Consolidated_AS-ONE'[D365 Base]="D365 Marketing", 100
    )

    Now, July 2025 will get the correct value of 600, as it is only included in date2. This should resolve the issue of incorrect values for July. Hope this helps in your scenerio