Custom column formula for a Power BI report

Question

There is an existing Power BI Query which is used\source for a Power BI report.

Custom Column

if [Session Admin]= "Not assigned Session" then 0 else [Quantity] * 95

How does the formula change if I have to consider date and month? say for example,

if [Session Admin]= "Not assigned Session" then 0 else [Quantity] * (if [Date] > 6/30/2021 then 100 else 95).

It is throwing Error if I use [Date] > 6/30/2021.

I want to actually use the 'Date' column from the screenshot.

Also, not sure if I need to enter the 12:00:00 AM time in the formula that is displayed as part of the 'Date' column.

Appreciated your help!

Thanks!

imkef · Answer

Hi&nbsp;BBIUser&nbsp;,please try the following syntax instead:if [Session Admin]= "Not assigned Session" then 0 else [Quantity] * (if&nbsp;[Date] &gt; #date(2012,6,30)&nbsp;then 100 else 95)
&nbsp;

jennratten · Answer

Hello -
Can you please try creating a new blank query and open the Advanced Editor (button on the ribbon).&nbsp; The replace the entire contents of the Advanced Editor with this script below?&nbsp; After you do this, in the formula bar, you will see the portion of the script below that is outlined in RED will be gone.

It will look like this:

Script to replace all contents in the Advanced Editor:
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtc30jcyMDJUMDSyMjAAIgVHXyUdpaDM5AyF8MScktQiIM9IKVYnWslS39ASm1q//BKFxOLizPS81BSF4FQgKz8PKGwK1mQE1WSE1wILiFqg2bqGhiDZ/KTMPIVgoHQVkGeJJGuE20IzpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Session Admin" = _t, Quantity = _t]),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(Source, {{"Date", each Text.BeforeDelimiter(_, " "), type text}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Extracted Text Before Delimiter",{{"Date", type date}, {"Quantity", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each 
if [Session Admin] = "Not assigned Session" then 0 else 
[Quantity] * ( if Date.From([Date]) &gt; #date(2021,6,30) then 100 else 95 ) 
)
in
    #"Added Custom"
Explanation:
Based on the screensnip below that you provided, it appeared as though the date/time values in your column were inconsistently formatted, with some being date/time and some being a date only as yyyy-mm.&nbsp; Please let me know if this assumption is incorrect and if so, can you please provide more information about the date values shown in your screenship with the yyyy-mm values?

Based on this assumption, the script I provided actually includes three new steps.&nbsp; These can be consolidated but I left them separate so they could be easily understood.&nbsp;
The script begins with the date column being formatted as text.&nbsp; It is important to begin this way if the values in the column have varying formats.&nbsp;

A step is then added to extract the portion of text that appears before the space, which leaves only the date (from both formats) and omits the time.&nbsp;

The proper types are then applied.&nbsp;&nbsp;

The new column can then be added, using the transformed dates.

&nbsp;

mahesh0016 · Answer

&nbsp;

bbiuser · Answer

ImkeF&nbsp;Mahesh0016&nbsp;Thanks for your responses!I get the same error and the formula did not workif [Session Admin]= "Not assigned Session" then 0 else [Quantity] * (if&nbsp;[Date] &gt;&nbsp;#date(2021,6,30)&nbsp;then 100 else 95)OrTable.AddColumn(#"Renamed Columns", "Amount", eachif [Session Admin]= "Not assigned Session" then 0 else [Quantity] * (if&nbsp;[Date] &gt;&nbsp;#date(2021,6,30)&nbsp;then 100 else 95) --&gt; Here the only difference between Mahesh formula and mine is I have 'Renamed columns' instead of 'Changed Type'&nbsp;Expression.Error: The Date operation failed because the resulting value falls outside the range of allowed values.&nbsp;The error says outside the range but I have data unitl 11/30/2022 and all records displays error. Below are some screenshots. Is it somethign to do with date and time format???&nbsp;&nbsp; &nbsp; &nbsp;&nbsp;Please let me know if you need any more info.Thanks!&nbsp;&nbsp;

jennratten · Answer

Hello - it looks like your date column may have varying formats.&nbsp; Please try this:
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtc30jcyMDJUMDSyMjAAIgVHXyUdpaDM5AyF8MScktQiIM8IhIGKdA0NlWJ1opUs9Q0tsenyyy9RSCwuzkzPS01RCE4FsvLzgMKmcO1GYO1GUO1GeC21gOgy0jWAWApzAFBdflJmnkIwUGEVkGcJV2eEpM4It3PM4BqMlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Session Admin" = _t, Quantity = _t, Column1 = _t]),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(Source, {{"Date", each Text.BeforeDelimiter(_, " "), type text}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Extracted Text Before Delimiter",{{"Date", type date}, {"Quantity", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each 
if [Session Admin] = "Not assigned Session" then 0 else 
[Quantity] * ( if Date.From([Date]) &gt; #date(2021,6,30) then 100 else 95 ) 
)
in
    #"Added Custom"

&nbsp;
&nbsp;

Forum Discussion

Custom column formula for a Power BI report

8 Replies

Related content

If formula

Custom Column Formula Help

formula

DAX Formula Help

Including Excel formulas in custom columns