CODEWITHSUNDEEP
powerbidax
Dinesh
Dinesh

Reputation: 421

Convert Numeric type 2000.50 to 2000/09/30 data format in powerbi

I'm trying to convert numeric column which have values as 2000.50/2000.75 and to date format as 2000/09/30 in PowerBI

VAR FiscalYear2 =
    LEFT ( Axio_QuarterlySupplyDemandModelNational[Year_Dup], 4 )
RETURN
    (
        SWITCH (
            RIGHT ( Axio_QuarterlySupplyDemandModelNational[Year_Dup], 3),
            ".00", "3/31/" & FiscalYear2,
            ".25", "6/30/" & FiscalYear2,
            ".50", "9/30/" & FiscalYear2,
            ".75", "12/31/" & FiscalYear2     
        )
)

I tried the attached code, but some of the values such as 2000.00/2000.50 are not converted as expected.

Upvotes: 1

Views: 55

Answers (1)

Ryan B.
Ryan B.

Reputation: 3665

There are a few approaches that I would think are better than trying to transform the decimal date value in DAX. In the comments, it's been recommended that you do your conversion in Power Query, which is a great suggestion, or use a date table.

We don't always have control over the model tho, so if an additional column on the reporting side is needed, I would suggest that this isn't a formatting question. Fractional years are not a standard date format. You have numbers, so your approach should not be to parse or format, but to calculate.

DateConverted_col = 
    VAR YearVal = TRUNC(Axio_QuarterlySupplyDemandModelNational[Year_Dup])
    VAR QuarterVal = Mod(Axio_QuarterlySupplyDemandModelNational[Year_Dup] * 4, 4) + 1

    RETURN Date(YearVal, QuarterVal * 3 + 1, 1) - 1

The TRUNC function drops the decimal portion of the number, leaving you with just the year.

Modulo needs a little massage for fractional numbers. Scaling up by 4 and then finding mod 4 will give you a 0-3 value. Add one and that's the Quarter we're working with.

Multiply the quarter by 3 to get the month number. Figure out that month's last day by adding a whole month to its first day, and then subtracting a single day. Presto, you have the last day of the quarter.

Hope it helps.

Upvotes: 1

Related Questions