how to create measure YTD for data based on formulas

[Ewa_28]

Hi,

I need to create YTD for data which was based on the following formula:

CombinedData =
UNION(
SELECTCOLUMNS(
'Stemple',
"Typ", "Sales",
"Sales", 'Stemple'[Sales], -- Sales value in a separate column
"Plany", BLANK(), -- Empty (blank) for the "Plany" column
"Person", 'Stemple'[Person],
"Week", 'Stemple'[Week],
"Q_numer", 'Stemple'[Q_numer],
"Region", 'Stemple'[Region],
"Year", 'Stemple'[Year]
),
SELECTCOLUMNS(
'Plany data',
"Typ", "Plany",
"Sales", BLANK(), -- Empty (blank) for the "Sales" column
"Plany", 'Plany data'[Value], -- Plany value in a separate column
"Person", 'Plany data'[Negocjator],
"Week", 'Plany data'[Week],
"Q_numer", 'Plany data'[Q_numer],
"Region", 'Plany data'[Region],
"Year", 'Plany data'[Year]
)
)

Solved: Re: data in seperating columns. How to do it? - Microsoft Fabric Community

I want to use slacer by week and quarter. 

Please help me.

Regards, Ewa

[Anonymous]

Hi, @Ewa_28 

Based on your information, I create sample tables:

Combine them:

CombinedData = 
UNION(
    SELECTCOLUMNS(
        'Stemple',
        "Typ", "Sales",
        "Sales", 'Stemple'[Sales],
        "Plany", BLANK(),
        "Person", 'Stemple'[Person],
        "Week", 'Stemple'[Week],
        "Q_numer", 'Stemple'[Q_numer],
        "Region", 'Stemple'[Region],
        "Year", 'Stemple'[Year]
    ),
    SELECTCOLUMNS(
        'Plany data',
        "Typ", "Plany",
        "Sales", BLANK(),
        "Plany", 'Plany data'[Value],
        "Person", 'Plany data'[Negocjator],
        "Week", 'Plany data'[Week],
        "Q_numer", 'Plany data'[Q_numer],
        "Region", 'Plany data'[Region],
        "Year", 'Plany data'[Year]
    )
)

Create a calendar table:

DateTable = 
CALENDAR(
    DATE(2023, 1, 1),
    DATE(2023, 12, 31)
)

Then create relationship, and create two measures:

SalesYTD = 
CALCULATE(
    SUM('CombinedData'[Sales]),
    DATESYTD('DateTable'[Date])
)

PlanyYTD = 
CALCULATE(
    SUM('CombinedData'[Plany]),
    DATESYTD('DateTable'[Date])
)

Here is my preview:

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

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

[Ewa_28]

hi,  unfortunatelly, it doesn't work 😞

[Ewa_28]

hi,  unfortunatelly, it doesn't work 😞

[rajendraongole1]

Hi @Ewa_28 - You need to create two separate YTD measures: one for Sales and one for Plany.

YTD Sales =
CALCULATE(
SUM('CombinedData'[Sales]),
FILTER(
ALL('CombinedData'),
'CombinedData'[Year] = MAX('CombinedData'[Year]) &&
'CombinedData'[Week] <= MAX('CombinedData'[Week])
)
)

measure 2: plany

YTD Plany =
CALCULATE(
SUM('CombinedData'[Plany]),
FILTER(
ALL('CombinedData'),
'CombinedData'[Year] = MAX('CombinedData'[Year]) &&
'CombinedData'[Week] <= MAX('CombinedData'[Week])
)
)

Use the Week and Q_numer columns from the CombinedData table in Slicers.
When you select a week or a quarter, the measures will automatically filter the data to show cumulative YTD values for the selected context.

Hope this works, please check