Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
RenierKotze
New Member

Please assist with DAX code

Please assist with DAX

 

I have created a DAX measure(please see below for my script). The result is not correct, I have tried troubleshooting but had no luck. 

 

PrevYearYTD =
VAR selecitem =
    IF (
        HASONEVALUE ( 'MEASURE'[Description] ),
        VALUES ( 'MEASURE'[Description] ),
        BLANK ()
    )
VAR selectedYear = MAX ( 'CALENDAR'[YEAR] )
VAR selectedMonth = MAX ( 'CALENDAR'[MONTH] )
VAR maxDateInTable = MAX ( 'EXBI_2023_YTD(MasterTable)'[Date])
VAR prevYearMaxDate = SAMEPERIODLASTYEAR(DATEADD('EXBI_2023_YTD(MasterTable)'[Date], -1, YEAR))

RETURN
    SWITCH (
        selecitem,
        "VALUE",
            CALCULATE (
                SUM ( 'EXBI_2023_YTD(MasterTable)'[Net Sales Invoiced] ),
                FILTER (
                    ALL ( 'CALENDAR' ),
                    'CALENDAR'[YEAR] = selectedYear &&
                    'CALENDAR'[MONTH] <= selectedMonth &&
                    'CALENDAR'[DATE_KEY] = maxDateInTable
                )
            ) ,
            "UNITS",
            CALCULATE(
                SUM('EXBI_2023_YTD(MasterTable)'[Sales quantity]),
                FILTER(
                    ALL('CALENDAR'),
                    'CALENDAR'[YEAR] = selectedYear &&
                    'CALENDAR'[MONTH] <= selectedMonth &&
                    'CALENDAR'[DATE_KEY] = maxDateInTable
                )
            ),BLANK()
    )
2 REPLIES 2
v-zhangti
Community Support
Community Support

Hi, @RenierKotze 

 

Can you provide sample data for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures or Excel. I look forward to your response.

 

Best Regards,

Community Support Team _Charlotte

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

Good Morning

 

Thank you I appreciate your help, here is some background. In my Power BI report, I would like the user/s to be able to select from sliecers the following:

1. Slicer 1, the user is able to select if he/she would like to see the result/visualization in "Units/Value". I did create a table called MEASURE[Description]. In the Description column we have Value/ or Units

2. Slicer 2, the user is able to select the Year, i have a Calendar table, with a column named as DATE_KEY. The data type =Date format, yyyy/mm/dd

3. Sliecer 3, the user is able to select the month, also from the Calendar table,  the Month is a Whole number data type

Relationships:

I created many to one relationships to my fact tables for the following:

1. Date_KEY to Fact tables(to Date columns, which is Date Type)(Calendar)

2. Product dimensions

 

I have 3 tabels(Fact)

1. Budget2023

2. Current year (append, I have a couple of Sales tabels, 1= MTD 2 ; 2=Y2022; 3= Y2021; YTD excluding the Month to date)

 

The issue I have is the Previous Year measure, as the measure calculates the full month where it should only do calculations up to 2023/11/21(example)

 

MainScreen.PNGMeasure YTD.PNGMeaure PY.PNG

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.