Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 ...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    7 years ago

    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)

     

adityavighne's avatar
adityavighne
Icon for Continued Contributor rankContinued Contributor
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

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 

 

 

  • adityavighne's avatar
    adityavighne
    Icon for Continued Contributor rankContinued Contributor
    7 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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)
        )
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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)