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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Formatting Year and Calendar Week

Hello, 

 

I have a data set, which includes a column for year (2018 and 2019) and a column for calendar week (1,2,3...)

 

Is it possible to format the columns so that they are recognized as year and calendar week ? 

 

The data set is too large for a daily basis data set. 


Thanks

Anil

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi,

have you a Year dimension table? Or have you just one large table?

 

If you have a table Calendar with the list of Year, you can create these measures:

TotalValue = SUM(Value)

TotalValue PY = CALCULATE([TotalValue], FILTER( ALL(Calendar), Calendar[Year] = MAX(Calendar[Year])-1))

Total Value YoY = TotalValue - TotalValue PY

% vs TotalVaue PY = DIVIDE(Total Value YoY,  TotalValue PY)

 

View solution in original post

6 REPLIES 6
adityavighne
Continued Contributor
Continued Contributor

Try this -

 

Year-Wk = YEAR ( DS_1[Execution Date] ) & "-" & CONCATENATE ( IF ( WEEKNUM ( DS_1[Execution Date],21 ) < 10, "0", "" ), WEEKNUM (DS_1[Execution Date] , 21) )
Anonymous
Not applicable

I believe my problem description was not well described from my side.

 

When I build graphs within Powerbi, I want to calculate the sales change in % between 2019 and 2018.

 

Under quick measures I can select the calculation " change in comparison to previous year". Aftwerwards, I need to select the data field, based on which the previous year calculation should be made.

 

The problem starts after this point. I cannot select the column "Year" as a basis for the previous year calculation. It is not accepted as a date format. Since I have only the year and not the day, I could not find a way to solve this issue.

 

Thanks for your time 

 

 

Anonymous
Not applicable

Hi,

have you a Year dimension table? Or have you just one large table?

 

If you have a table Calendar with the list of Year, you can create these measures:

TotalValue = SUM(Value)

TotalValue PY = CALCULATE([TotalValue], FILTER( ALL(Calendar), Calendar[Year] = MAX(Calendar[Year])-1))

Total Value YoY = TotalValue - TotalValue PY

% vs TotalVaue PY = DIVIDE(Total Value YoY,  TotalValue PY)

 

So you want to calculate Year-on -Year % change?

Anonymous
Not applicable

Yes, but my column  "year" is not recognized in the selection field as a valid option.

Anonymous
Not applicable

Even though I tricked and entered fake dates I recieved the following error message (sorry for german)

 

Netto Umsatz YoY% 2 =
IF(
    ISFILTERED('powerbi'[Datum]);
    ERROR("Quickmeasures mit Zeitintelligenz können nur über die von Power BI bereitgestellte Datumshierarchie oder die primäre Datumsspalte gruppiert oder gefiltert werden.");
    VAR __PREV_YEAR =
        CALCULATE(
            SUM('powerbi'[Netto Umsatz]);
            DATEADD('powerbi'[Datum].[Date]; -1; YEAR)
        )
    RETURN
        DIVIDE(SUM('powerbi'[Netto Umsatz]) - __PREV_YEAR; __PREV_YEAR)
)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.