Compare Daily Sales Vs Limit

[rahulvyas]

Hello, 

Does anyone know how to compare the daily sales of a store with a given limit? I am trying to find out if the total daily sales irrespective of Currency are exceeding their limits per store. The limit table keeps on changing based on demand. Trying to build something with time intelligence so we can compare how many times the store went over its limits per year, quarter, or any different type of analysis.

Sales Table
PostDate	Branch	Currency	Balance	Limt
2020-03-17	Store 1	GBP	280713.49	$   200,000.00
2020-03-17	Store 1	USD	11825.00	$   200,000.00
2020-03-17	Store 1	CAD	5785.00	$   200,000.00
2020-03-18	Store 1	USD	33424.00	$   300,000.00
2020-03-18	Store 1	CAD	-11228.00	$   300,000.00
2020-03-19	Store 1	CAD	0.00	$   300,000.00
2020-03-19	Store 1	EUR	202.50	$   300,000.00
2020-03-19	Store 1	GBP	74.00	$   300,000.00
2020-03-19	Store 1	USD	200.78	$   300,000.00
2020-11-16	Store 1	CAD	60.00	$   300,000.00
2020-11-16	Store 1	CAD	164580.00	$   300,000.00
2020-11-16	Store 1	USD	11825.00	$   300,000.00
2021-11-16	Store 1	CAD	5785.00	$   200,000.00
2021-11-16	Store 1	CAD	33753.00	$   200,000.00
2021-11-17	Store 1	CAD	290.66	$   200,000.00
2021-11-17	Store 1	CAD	2314.00	$   200,000.00
2022-03-10	Store 1	CAD	200.10	$   220,000.00
2022-03-10	Store 1	CAD	303.00	$   220,000.00
2022-03-11	Store 1	USD	200.28	$   220,000.00
2022-03-11	Store 1	CAD	56998.10	$   220,000.00
2022-03-12	Store 1	EUR	60134.32	$   220,000.00

Limit Table
Date	Store Current Limit	Old Limit
11/5/2018	$200,000.00
3/18/2020	$300,000.00	$200,000.00
8/17/2021	$200,000.00	$300,000.00
3/7/2022	$220,000.00	$200,000.00

[Whitewater100]

Hi:

Please find attached link comparing credit limit during period to balance. A new Limit table and Fact Table are in the model. I altered the format of the limit table so it can be used a bit easier down the road.

You can probably take it much further in terms of these changing dimensions by store and date, however I think this is what you are looking for for now. I hope so! Thanks

https://drive.google.com/file/d/1dziPjkIk2ub9vofzGUTu8krkl58aKZ7K/view?usp=sharing 

[VahidDM · Accepted solution]

Hi @rahulvyas 

Try this measure to find the total number of days with overrun:

Over The Limit = 
VAR _A =
    SUMMARIZE (
        'Sales Table',
        'Sales Table'[PostDate],
        "Sale", SUM ( 'Sales Table'[Balance] )
    )
VAR _B =
    ADDCOLUMNS (
        _A,
        "Limit",
            CALCULATE (
                MAX ( 'Limit Table'[ Store Current Limit  ] ),
                FILTER (
                    ALL ( 'Limit Table' ),
                    'Limit Table'[Date]
                        = CALCULATE (
                            MAX ( 'Limit Table'[Date] ),
                            FILTER ( ALL ( 'Limit Table' ), 'Limit Table'[Date] <= EARLIER ( [PostDate] ) )
                        )
                )
            )
    )
VAR _C =
    ADDCOLUMNS ( _B, "Over", IF ( [Sale] > [Limit], 1, 0 ) )
RETURN
    SUMX ( _C, [Over] )

Download the sample file attached.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/