Capture date from Date Slicer

Power BI
21

Hi Brian,

I’m sorry, now when I look back again, it’s correct. Only in the matrix it does not put in the right column.
Please see attached.Testing_AgingAdventureWorksDW_Import - solution3.pbix (738.2 KB)

Thanks,

22

@axaeffect,

One of the things I find most interesting about Power BI is that there are often many ways to accomplish the same result. Thus, while I’m sure there is a way to use dynamic segmentation to build this table, I would take a much simpler approach:

  1. create a measure (e.g., [Aging Category] ) that assigns the proper category from your range table based on the [Aging with Total] measure.

You could do this with nested IF statements, but with four categories in your range table, this will get pretty messy. Instead, it’s a lot cleaner to use a SWITCH ( TRUE() ) construct:

  1. Create four new measures, one for each category in your range table, along the lines of:

    Total Sales A>30 =
    CALCULATE (
    IF ( [Aging Category] = “>30”, [Total Sales], BLANK () ),
    ALLSELECTED ( DimDate )
    )

Then drop those four new measures into your table visual, and you should be good to go. (Note: to get the proper grand totals for each column you may need to use the HASONEVALUE construct we discussed above).

See how this works for you.

  • Brian
23

Hi Brian,

I found a solution. It turn out I need to specify the column, instead just saying the table.

With the above formula for range, so this is the correct one:

Aging with total correct = CALCULATE(
                                [Total sales amount],
                                FILTER(
                                    ADDCOLUMNS(
                                    **VALUES(FactInternetSales[SalesOrderNumber])**,
                                    "ABCD", 
                                    CALCULATE (
                                            [Aging],
                                            CALCULATETABLE (
                                                            **VALUES(FactInternetSales[SalesOrderNumber])**
                                                            ),
                                                            ALLSELECTED()
                                                        )
                                    ),
                                    COUNTROWS(
                                        FILTER(Range,
                                        [Aging] >= [Minimum] && 
                                        [Aging] <=[Maximum]
                                        )
                                    ) > 0
                            )
                        )

The one that I put bold, are the only thing needs to be added from the previous.
Many thanks for your kind help all this time.