Help... Variable Column Data

[johankent30]

I have a rather complicated project that I have undertaken and am stuck. I want to be able to segment accounts receivable data by collections and sales orders. Collections data is coming from a table called AR and sales orders are coming from a input parameter calles Sales Orders. Another twist is that I want the collections data to be variable based on the Scenario that is selected. So if the "Due Date" scenario is selected the value should be NetAR based on the due date column. But if the scenario selected is "DOS" then the Forecasted Paid Date column should be used which is the due date + the AR parameter. The end result would be the Goal column. 

I tried using columns with IF statements but that is not working because the Parameters are not able to be changed. I also tried using a measure, but I don't know how to segment the measure so that the value is different based on if the row category is collections or sales order. 

Any thoughts?

I 

[v-juanli-msft]

Hi @johankent30 

Create a measure

Measure 3 = IF(HASONEVALUE(Merge1[date value]),[Measure 2],SUMX(Merge1,[Measure 2]))

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[johankent30]

@v-juanli-msft Can you take a look at this problem that I've ran into with the dates on the model you helped me on?

https://community.powerbi.com/t5/Desktop/Create-new-table-with-row-for-every-day/m-p/758107#M365353

Thank you!!!

[johankent30]

@v-juanli-msft You are a life saver! Thank you again!

[v-juanli-msft · Accepted solution]

Hi @johankent30 

In Edit queries, merge queries to a new query,

Remove "Date" column, expand "AR.NetAR", "AR.Due Date", "AR.Forecasted Paid Date" columns,

unpivot columns for "AR.Due Date", "AR.Forecasted Paid Date" columns,

rename columns' name: "Value"-> "date value", "Attribute"->"date type",

Close&&apply

Create measures in "Merge1" table

Measure =
SWITCH (
    SELECTEDVALUE ( Scenario[Scenario] ),
    "Due Date", CALCULATE (
        SUM ( Merge1[AR.NetAR] ),
        FILTER ( Merge1, Merge1[date type] = "AR.Due Date" )
    ),
    "Dos", CALCULATE (
        SUM ( Merge1[AR.NetAR] ),
        FILTER ( Merge1, Merge1[date type] = "AR.Forecasted Paid Date" )
    )
)

Measure 2 = IF(MAX(Merge1[Subcategory])="collections",[sales order parameter],[Measure])

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[johankent30]

@v-juanli-msft  Wow thanks for the help! One issue that I ran into with your example file is that the totals are not correct for Measure 2. Is there a way to make the totals show the correct sum based on each column? Thanks again for the help, what you did is awesome!!